📄 sal_qry_saleplanofdeptanalyse.pas
字号:
+#13+' '
+#13+' Plansum_11, '
+#13+' rationsum_11, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=11 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)=11 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_11, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=11 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)=11 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' -Plansum_11 as DiffAmount11, '
+#13+' case when Plansum_11=0 then 0 '
+#13+' else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=11 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)=11 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' )/Plansum_11 *100 end as ratePlan11, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=11 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)=11 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' -rationsum_11 as DiffrationAmount11, '
+#13+' case when rationsum_11=0 then 0 '
+#13+' else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=11 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)=11 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' )/rationsum_11 *100 end as rateration11 , '
+#13+' '
+#13+' Plansum_12, '
+#13+' rationsum_12, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=12 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)=12 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_12, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=12 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)=12 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' -Plansum_12 as DiffAmount12, '
+#13+' case when Plansum_12=0 then 0 '
+#13+' else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=12 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)=12 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' )/Plansum_12 *100 end as ratePlan12, '
+#13+' sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=12 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)=12 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' -rationsum_12 as DiffrationAmount12, '
+#13+' case when rationsum_12=0 then 0 '
+#13+' else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=12 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)=12 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) '
+#13+' )/rationsum_12 *100 end as rateration12 '
+#13+' into #tMpSalePlan_Dept '
+#13+' from Sal_SalePlan_Dept '
+#13+' left join Dept on Sal_SalePlan_Dept.DeptCode=Dept.DeptCode '
+#13+' left join sa_ShipOrder on Sal_SalePlan_Dept.DeptCode=sa_ShipOrder.SaleDeptCode and sa_ShipOrder.assess=1 '
+#13+' left join sa_BackShipOrder on Sal_SalePlan_Dept.DeptCode=sa_BackShipOrder.SaleDeptCode and sa_BackShipOrder.assess=1 '
+#13+' where Sal_SalePlan_Dept.DeptCode between '+quotedstr(DeptCode1)+' and '+quotedstr(DeptCode2)
+#13+' and Sal_SalePlan_Dept.PlanYear='+Year
+#13+' group by PlanYear,Sal_SalePlan_Dept.DeptCode,Dept.DeptName ,Plansum_Year,rationsum_Year, '
+#13+' Plansum_1,rationsum_1, '
+#13+' Plansum_2,rationsum_2, '
+#13+' Plansum_3,rationsum_3, '
+#13+' Plansum_4,rationsum_4, '
+#13+' Plansum_5,rationsum_5, '
+#13+' Plansum_6,rationsum_6, '
+#13+' Plansum_7,rationsum_7, '
+#13+' Plansum_8,rationsum_8, '
+#13+' Plansum_9,rationsum_9, '
+#13+' Plansum_10,rationsum_10, '
+#13+' Plansum_11,rationsum_11, '
+#13+' Plansum_12,rationsum_12 ';
Executesql(AdoQry_tmp,sqltext,1) ;
//****************************************************************************************************88888888888888
selectfromsql:='select * from #tMpSalePlan_Dept';
Orderbyfields:=' PlanYear,DeptCode ';
getdata;
lbl_Condition.Caption:='年份：'+Year+' /销售部门代码从 '+DeptCode1+' 到 '+DeptCode2;
end;
procedure TFrm_Sal_Qry_SalePlanOfDeptAnalyse.FormDestroy(Sender: TObject);
begin
inherited;
Frm_Sal_Qry_SalePlanOfDeptAnalyse:=nil;
end;
procedure TFrm_Sal_Qry_SalePlanOfDeptAnalyse.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
inherited;
try
Executesql(AdoQry_tmp,'drop table #tMpSalePlan_Dept',1);
except
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -