sal_qry_saleplanofdeptanalyse.pas

一个MRPII系统源代码版本

            +#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 )                                   '
            +#13+  '        -Plansum_6 as DiffAmount6,                                                                                                                                                                   '
            +#13+  '        case when Plansum_6=0 then 0                                                                                                                                                                               '
            +#13+  '        else (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 )                              '
            +#13+  '             )/Plansum_6 *100 end as ratePlan6,                                                                                                                                                           '
            +#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 )                                   '
            +#13+  '        -rationsum_6 as DiffrationAmount6,                                                                                                                                                         '
            +#13+  '        case when rationsum_6=0 then 0                                                                                                                                                                             '
            +#13+  '        else (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 )                              '
            +#13+  '             )/rationsum_6 *100 end as rateration6 ,                                                                                                                                                    '
            +#13+  '                                                                                                                                                                                                                   '
            +#13+  '         Plansum_7,                                                                                                                                                                                                '
            +#13+  '         rationsum_7,                                                                                                                                                                                              '
            +#13+  '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=7 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)=7 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_7,    '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=7 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)=7 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                    '
            +#13+  '       -Plansum_7 as DiffAmount7,                                                                                                                                                                    '
            +#13+  '       case when Plansum_7=0 then 0                                                                                                                                                                                '
            +#13+  '       else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=7 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)=7 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                               '
            +#13+  '            )/Plansum_7 *100 end as ratePlan7,                                                                                                                                                            '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=7 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)=7 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+  '        -rationsum_7 as DiffrationAmount7,                                                                                                                                                         '
            +#13+  '        case when rationsum_7=0 then 0                                                                                                                                                                             '
            +#13+  '        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=7 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)=7 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+  '             )/rationsum_7 *100 end as rateration7 ,                                                                                                                                                    '
            +#13+  '                                                                                                                                                                                                                   '
            +#13+  '         Plansum_8,                                                                                                                                                                                                '
            +#13+  '         rationsum_8,                                                                                                                                                                                              '
            +#13+  '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=8 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)=8 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_8,  '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=8 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)=8 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+  '        -Plansum_8 as DiffAmount8,                                                                                                                                                                   '
            +#13+  '        case when Plansum_8=0 then 0                                                                                                                                                                               '
            +#13+  '        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=8 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)=8 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+  '             )/Plansum_8 *100 end as ratePlan8,                                                                                                                                                           '
            +#13+  '        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=8 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)=8 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+  '        -rationsum_8 as DiffrationAmount8,                                                                                                                                                         '
            +#13+  '        case when rationsum_8=0 then 0                                                                                                                                                                             '
            +#13+  '        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=8 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)=8 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+  '             )/rationsum_8 *100 end as rateration8 ,                                                                                                                                                    '
            +#13+  '                                                                                                                                                                                                                   '
            +#13+  '         Plansum_9,                                                                                                                                                                                                '
            +#13+ '          rationsum_9,                                                                                                                                                                                              '
            +#13+'         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=9 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)=9 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_9,   '
            +#13+'        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=9 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)=9 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+'        -Plansum_9 as DiffAmount9,                                                                                                                                                                   '
            +#13+'        case when Plansum_9=0 then 0                                                                                                                                                                               '
            +#13+'        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=9 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)=9 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+'             )/Plansum_9 *100 end as ratePlan9,                                                                                                                                                           '
            +#13+'        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=9 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)=9 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                   '
            +#13+'        -rationsum_9 as DiffrationAmount9,                                                                                                                                                         '
            +#13+'        case when rationsum_9=0 then 0                                                                                                                                                                             '
            +#13+'        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=9 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)=9 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                              '
            +#13+'             )/rationsum_9 *100 end as rateration9 ,                                                                                                                                                    '
            +#13+'                                                                                                                                                                                                                   '
            +#13+'         Plansum_10,                                                                                                                                                                                               '
            +#13+'         rationsum_10,                                                                                                                                                                                             '
            +#13+'         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=10 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)=10 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_10,   '
            +#13+'        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=10 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)=10 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+'        -Plansum_10 as DiffAmount10,                                                                                                                                                                 '
            +#13+'        case when Plansum_10=0 then 0                                                                                                                                                                              '
            +#13+'        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=10 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)=10 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+'             )/Plansum_10 *100 end as ratePlan10,                                                                                                                                                         '
            +#13+'        sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=10 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)=10 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+'        -rationsum_10 as DiffrationAmount10,                                                                                                                                                       '
            +#13+'        case when rationsum_10=0 then 0                                                                                                                                                                            '
            +#13+'        else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=10 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)=10 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                             '
            +#13+'             )/rationsum_10 *100 end as rateration10 ,                                                                                                                                                  '