客戶銷售業績統計表

問題１ :

客戶銷貨業績統計表：在b子公司內，看的到a子公司的客戶業績資料。反之也是，正確應該只看到該子公司的客戶銷售業績。

select * from Systems
select * from PubFunction where i_system = 'A00000000003'
select * from PubFunction where i_functionName like '%客戶銷貨業績統計%'

TFormReportCusObject  SaleB.dll


select A.i_custom,A.i_customNo,A.i_customName
     , SUM(ISNULL(SlipAmount,0)*i_execRate) SlipAmount
     , SUM(ISNULL(ReturnAmount,0)*i_execRate) ReturnAmount
     , SUM(ISNULL(SlipAmount,0)*i_execRate)-SUM(ISNULL(ReturnAmount,0)*i_execRate) Diff
     , SUM(ISNULL(SlipCost,0)) SlipCost, SUM(ISNULL(ReturnCost,0)) ReturnCost
     , SUM(ISNULL(SlipProfit,0)) SlipProfit,SUM(ISNULL(ReturnProfit,0)) ReturnProfit
     ,SUM(ISNULL(i_tax,0)*i_execRate) TAX
     FROM
(     select A.i_execRate,A.i_pactNo,A.i_actDate,A.i_custom,A.i_customNo,A.i_customName,i_tax
,     CASE WHEN A.i_type>0 THEN  i_amount ELSE 0 END SlipAmount
,     CASE WHEN A.i_type=0 THEN  (-1)*i_amount ELSE 0 END ReturnAmount
,     sum(CASE WHEN A.i_type>0 THEN  i_cost ELSE 0 END) SlipCost
,     sum(CASE WHEN A.i_type<=0 THEN  i_cost ELSE 0 END) ReturnCost
,     sum(CASE WHEN A.i_type>0 THEN  i_profit ELSE 0 END) SlipProfit
,     sum(CASE WHEN A.i_type<=0 THEN  i_profit ELSE 0 END) ReturnProfit  
from vSaleSlipDetail A left join SaleCustom SC on A.i_custom=SC.i_custom
where SC.i_company = '' and
 A.i_actDate>='2011/1/1'  and    A.i_actDate<='2014/2/12'
 
group by  A.i_pactNo,A.i_actDate,A.i_custom,i_customNo,i_customName,i_amount,i_type,i_tax,A.i_execRate )  A
GROUP BY A.i_custom,A.i_customNo,A.i_customName ORDER BY i_customNo

增加公司廠商條件