sal_qry_saleplanofitemanalyse.dfm

一个MRPII系统源代码版本

        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_2 end as ratePlan2,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=2 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_2 as DiffrationAmount2,--实际较计划数量'
      '      case when rationsum_2=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=2 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_2 end as rateration2, --计划数量完成率'
      ''
      '       Plansum_3,'
      '       rationsum_3,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=3 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount3,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=3 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=3 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty3,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=3 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_3 as DiffAmount3,--实际较计划额'
      '      case when Plansum_3=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=3 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_3 end as ratePlan3,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=3 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=3 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_3 as DiffrationAmount3,--实际较计划数量'
      '      case when rationsum_3=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=3 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=3 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_3 end as rateration3 ,--计划数量完成率'
      ''
      '       Plansum_4,'
      '       rationsum_4,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=4 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=4 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount4,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=4 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=4 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty4,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=4 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=4 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_4 as DiffAmount4,--实际较计划额'
      '      case when Plansum_4=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=4 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=4 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_4 end as ratePlan4,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=4 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=4 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_4 as DiffrationAmount4,--实际较计划数量'
      '      case when rationsum_4=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=4 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=4 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_4 end as rateration4, --计划数量完成率'
      ''
      '       Plansum_5,'
      '       rationsum_5,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=5 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=5 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount5,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=5 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=5 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty5,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=5 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=5 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_5 as DiffAmount5,--实际较计划额'
      '      case when Plansum_5=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=5 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=5 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_5 end as ratePlan5,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=5 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=5 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_5 as DiffrationAmount5,--实际较计划数量'
      '      case when rationsum_5=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=5 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=5 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_5 end as rateration5 ,--计划数量完成率'
      ''
      '       Plansum_6,'
      '       rationsum_6,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=6 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=6 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount6,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=6 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=6 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty6,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=6 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=6 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_6 as DiffAmount6,--实际较计划额'
      '      case when Plansum_6=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=6 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=6 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_6 end as ratePlan6,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=6 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=6 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_6 as DiffrationAmount6,--实际较计划数量'
      '      case when rationsum_6=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=6 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=6 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_6 end as rateration6 ,--计划数量完成率'
      ''
      '       Plansum_7,'
      '       rationsum_7,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=7 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=7 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount7,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=7 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=7 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty7,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=7 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=7 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_7 as DiffAmount7,--实际较计划额'
      '      case when Plansum_7=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=7 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=7 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_7 end as ratePlan7,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=7 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=7 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_7 as DiffrationAmount7,--实际较计划数量'
      '      case when rationsum_7=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=7 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=7 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_7 end as rateration7 ,--计划数量完成率'
      ''
      '       Plansum_8,'
      '       rationsum_8,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=8 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=8 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount8,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=8 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=8 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty8,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=8 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=8 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_8 as DiffAmount8,--实际较计划额'
      '      case when Plansum_8=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=8 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=8 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_8 end as ratePlan8,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=8 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=8 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_8 as DiffrationAmount8,--实际较计划数量'
      '      case when rationsum_8=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=8 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=8 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_8 end as rateration8 ,--计划数量完成率'
      ''
      '       Plansum_9,'
      '       rationsum_9,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=9 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=9 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount9,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=9 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty9,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=9 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=9 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_9 as DiffAmount9,--实际较计划额'
      '      case when Plansum_9=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=9 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=9 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_9 end as ratePlan9,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=9 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_9 as DiffrationAmount9,--实际较计划数量'
      '      case when rationsum_9=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=9 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_9 end as rateration9 ,--计划数量完成率'
      ''
      '       Plansum_10,'
      '       rationsum_10,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=10 t' +
        'hen isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'