sal_qry_saleplanofdeptanalyse.pas

一个MRPII系统源代码版本

            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/Plansum_1 *100 end as ratePlan1,                                                                                                                                                          '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                 '
            +#13+  '         -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -rationsum_1 as DiffrationAmount1,                                                                                                                                                      '
            +#13+ '         case when rationsum_1=0 then 0                                                                                                                                                                            '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/rationsum_1 *100 end as rateration1,                                                                                                                                                   '
            +#13+ '                                                                                                                                                                                                                   '
            +#13+ '          Plansum_2,                                                                                                                                                                                               '
            +#13+ '          rationsum_2,                                                                                                                                                                                             '
            +#13+ '          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                               '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_2,  '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -Plansum_2 as DiffAmount2,                                                                                                                                                                '
            +#13+ '         case when Plansum_2=0 then 0                                                                                                                                                                              '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/Plansum_2 *100 end as ratePlan2,                                                                                                                                                       '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -rationsum_2 as DiffrationAmount2,                                                                                                                                                       '
            +#13+ '         case when rationsum_2=0 then 0                                                                                                                                                                            '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/rationsum_2 *100 end as rateration2,                                                                                                                                                  '
            +#13+ '                                                                                                                                                                                                                   '
            +#13+ '          Plansum_3,                                                                                                                                                                                               '
            +#13+ '          rationsum_3,                                                                                                                                                                                             '
            +#13+ '          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                               '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_3,   '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -Plansum_3 as DiffAmount3,                                                                                                                                                                 '
            +#13+ '         case when Plansum_3=0 then 0                                                                                                                                                                              '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/Plansum_3 *100 end as ratePlan3,                                                                                                                                                       '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -rationsum_3 as DiffrationAmount3,                                                                                                                                                       '
            +#13+ '         case when rationsum_3=0 then 0                                                                                                                                                                            '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=3 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=3 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/rationsum_3 *100 end as rateration3 ,                                                                                                                                                  '
            +#13+ '                                                                                                                                                                                                                   '
            +#13+ '          Plansum_4,                                                                                                                                                                                               '
            +#13+ '          rationsum_4,                                                                                                                                                                                             '
            +#13+ '          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                               '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_4,  '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -Plansum_4 as DiffAmount4,                                                                                                                                                                '
            +#13+ '         case when Plansum_4=0 then 0                                                                                                                                                                              '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/Plansum_4 *100 end as ratePlan4,                                                                                                                                                          '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -rationsum_4 as DiffrationAmount4,                                                                                                                                                        '
            +#13+ '         case when rationsum_4=0 then 0                                                                                                                                                                            '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=4 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=4 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+ '              )/rationsum_4 *100 end as rateration4,                                                                                                                                                    '
            +#13+ '                                                                                                                                                                                                                   '
            +#13+ '          Plansum_5,                                                                                                                                                                                               '
            +#13+ '          rationsum_5,                                                                                                                                                                                              '
            +#13+ '          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+  '         -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_5,    '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                 '
            +#13+  '         -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+  '        -Plansum_5 as DiffAmount5,                                                                                                                                                                   '
            +#13+  '        case when Plansum_5=0 then 0                                                                                                                                                                               '
            +#13+  '        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                           '
            +#13+  '              -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+  '             )/Plansum_5 *100 end as ratePlan5,                                                                                                                                                           '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                 '
            +#13+  '         -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+  '        -rationsum_5 as DiffrationAmount5,                                                                                                                                                         '
            +#13+  '        case when rationsum_5=0 then 0                                                                                                                                                                             '
            +#13+  '        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=5 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                           '
            +#13+  '              -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=5 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+  '             )/rationsum_5 *100 end as rateration5 ,                                                                                                                                                    '
            +#13+  '                                                                                                                                                                                                                   '
            +#13+  '         Plansum_6,                                                                                                                                                                                                '
            +#13+  '         rationsum_6,                                                                                                                                                                                              '
            +#13+  '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=6 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+  '         -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=6 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_6,    '