sal_qry_saleplanofsaleranalyse.dfm

一个MRPII系统源代码版本

        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_3 as DiffAmount3,--实际较计划额'
      '      case when Plansum_3=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_3 end as ratePlan3,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_3 as DiffrationAmount3,--实际较计划定额'
      '      case when rationsum_3=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_3 end as rateration3 ,--计划定额完成率'
      ''
      '       Plansum_4,'
      '       rationsum_4,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_4 as DiffAmount4,--实际较计划额'
      '      case when Plansum_4=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_4 end as ratePlan4,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_4 as DiffrationAmount4,--实际较计划定额'
      '      case when rationsum_4=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_4 end as rateration4, --计划定额完成率'
      ''
      '       Plansum_5,'
      '       rationsum_5,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_5 as DiffAmount5,--实际较计划额'
      '      case when Plansum_5=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_5 end as ratePlan5,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_5 as DiffrationAmount5,--实际较计划定额'
      '      case when rationsum_5=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_5 end as rateration5 ,--计划定额完成率'
      ''
      '       Plansum_6,'
      '       rationsum_6,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_6 as DiffAmount6,--实际较计划额'
      '      case when Plansum_6=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_6 end as ratePlan6,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_6 as DiffrationAmount6,--实际较计划定额'
      '      case when rationsum_6=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_6 end as rateration6 ,--计划定额完成率'
      ''
      '       Plansum_7,'
      '       rationsum_7,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=7 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=7 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=7 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=7 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_7 as DiffAmount7,--实际较计划额'
      '      case when Plansum_7=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=7 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=7 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_7 end as ratePlan7,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=7 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=7 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_7 as DiffrationAmount7,--实际较计划定额'
      '      case when rationsum_7=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=7 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=7 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_7 end as rateration7 ,--计划定额完成率'
      ''
      '       Plansum_8,'
      '       rationsum_8,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=8 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=8 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=8 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=8 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_8 as DiffAmount8,--实际较计划额'
      '      case when Plansum_8=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=8 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=8 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_8 end as ratePlan8,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=8 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=8 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_8 as DiffrationAmount8,--实际较计划定额'
      '      case when rationsum_8=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=8 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=8 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_8 end as rateration8 ,--计划定额完成率'
      ''
      '       Plansum_9,'
      '       rationsum_9,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=9 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=9 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=9 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=9 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_9 as DiffAmount9,--实际较计划额'
      '      case when Plansum_9=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=9 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=9 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_9 end as ratePlan9,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=9 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=9 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'