📄 sal_qry_saleplanofitemanalyse.pas
字号:
+' -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.Createdate)=3 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_3 *100 end as rateration3 , '
+' '
+' Plansum_4, '
+' rationsum_4, '
+' 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.Createdate)=4 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount4, '
+' 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.Createdate)=4 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty4, '
+' 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.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.Createdate)=4 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_4 *100 end as ratePlan4, '
+' 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.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.Createdate)=4 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_4 *100 end as rateration4, '
+' '
+' Plansum_5, '
+' rationsum_5, '
+' 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.Createdate)=5 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount5, '
+' 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.Createdate)=5 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty5, '
+' 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.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.Createdate)=5 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_5 *100 end as ratePlan5, '
+' 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.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.Createdate)=5 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_5 *100 end as rateration5 , '
+' '
+' Plansum_6, '
+' rationsum_6, '
+' 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.Createdate)=6 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount6, '
+' 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.Createdate)=6 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty6, '
+' 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.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.Createdate)=6 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_6 *100 end as ratePlan6, '
+' 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.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.Createdate)=6 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_6 *100 end as rateration6 , '
+' '
+' Plansum_7, '
+' rationsum_7, '
+' 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.Createdate)=7 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount7, '
+' 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.Createdate)=7 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty7, '
+' 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.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.Createdate)=7 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_7 *100 end as ratePlan7, '
+' 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.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.Createdate)=7 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_7 *100 end as rateration7 , '
+' '
+' Plansum_8, '
+' rationsum_8, '
+' 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.Createdate)=8 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount8, '
+' 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.Createdate)=8 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty8, '
+' 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.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.Createdate)=8 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_8 *100 end as ratePlan8, '
+' 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.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.Createdate)=8 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_8 *100 end as rateration8 , '
+' '
+' Plansum_9, '
+' rationsum_9, '
+' 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.Createdate)=9 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount9, '
+' 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.Createdate)=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty9, '
+' 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.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.Createdate)=9 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_9 *100 end as ratePlan9, '
+' 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.Createdate)=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_9 as DiffrationAmount9, '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -