📄 sal_qry_saleplanofitemanalyse.pas
字号:
+' 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.Createdate)=9 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_9 *100 end as rateration9 , '
+' '
+' Plansum_10, '
+' rationsum_10, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount10, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty10, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_10 as DiffAmount10, '
+' case when Plansum_10=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_10 *100 end as ratePlan10, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_10 as DiffrationAmount10, '
+' case when rationsum_10=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=10 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_10 *100 end as rateration10 , '
+' '
+' Plansum_11, '
+' rationsum_11, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount11, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty11, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_11 as DiffAmount11, '
+' case when Plansum_11=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_11 *100 end as ratePlan11, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_11 as DiffrationAmount11, '
+' case when rationsum_11=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=11 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_11 *100 end as rateration11 , '
+' '
+' Plansum_12, '
+' rationsum_12, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount12, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty12, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_12 as DiffAmount12, '
+' case when Plansum_12=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_12 *100 end as ratePlan12, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_12 as DiffrationAmount12, '
+' case when rationsum_12=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=12 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_12 *100 end as rateration12 '
+' '
+' into #Sal_SalePlan_Item_Qry '
+' from Sal_SalePlan_Item '
+' left join Item on Sal_SalePlan_Item.ItemCode=Item.ItemCode '
+' left join #sa_ShipOrder on Sal_SalePlan_Item.ItemCode=#sa_ShipOrder.ItemCode '
+' left join #sa_BackShipOrder on Sal_SalePlan_Item.ItemCode=#sa_BackShipOrder.ItemCode '
+' where Sal_SalePlan_Item.PlanYear='+Year
+' group by PlanYear,Sal_SalePlan_Item.ItemCode,Item.ItemName ,Plansum_Year,rationsum_Year, '
+' Plansum_1,rationsum_1, '
+' Plansum_2,rationsum_2, '
+' Plansum_3,rationsum_3, '
+' Plansum_4,rationsum_4, '
+' Plansum_5,rationsum_5, '
+' Plansum_6,rationsum_6, '
+' Plansum_7,rationsum_7, '
+' Plansum_8,rationsum_8, '
+' Plansum_9,rationsum_9, '
+' Plansum_10,rationsum_10, '
+' Plansum_11,rationsum_11, '
+' Plansum_12,rationsum_12 ';
Executesql(AdoQry_tmp,sqltext,1) ;
//****************************************************************************************************88888888888888
selectfromsql:='select * from #Sal_SalePlan_Item_Qry';
Orderbyfields:=' PlanYear,ItemCode ';
getdata;
lbl_Condition.Caption:='年份：'+Year+' /产品代码从 '+ItemCode1+' 到 '+ItemCode2;
end;
procedure TFrm_Sal_Qry_SalePlanOfItemAnalyse.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Sal_Qry_SalePlanOfItemAnalyse:=nil;
end;
procedure TFrm_Sal_Qry_SalePlanOfItemAnalyse.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
inherited;
try
Executesql(AdoQry_tmp,'drop table #Sal_SalePlan_Item_Qry',1);
except
end;
try
Executesql(AdoQry_tmp,'drop table #sa_ShipOrder',1);
except
end;
try
Executesql(AdoQry_tmp,'drop table #sa_BackShipOrder',1);
except
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -