各省销售统计--会计凭证和发货单的联动

老板想看的总是你不记账的，这是规定！

--各省销售统计
select a.年,a.月,a.大区,a.省区经理,a.省份,sum(发货金额) as 发货金额,sum(回款金额) as 回款金额
from (
select distinct ua.iId as 月,ua.iYear as 年,dep.cDepCode as 部门编码,dep.cDepName as 大区,
psn.cPsn_Name as 省区经理,cusc1.cCCName as 省份
from Customer cust
left join CustomerClass cusc on cust.cCCCode = cusc.cCCCode
left join CustomerClass cusc1 on left(cusc.cCCCode,2) = cusc1.cCCCode
left join Department dep on cust.cCusDepart = dep.cDepCode
left join hr_hi_person psn on cusc1.cCCName = psn.cPsnFAddr and psn.cPsnNowAddress = '省区经理'
cross join UA_Period ua
cross join fitemss00 fits
where ua.cAcc_Id ='***'
and fits.citemcode in ('***')
) a
left join (
select b1.年,b1.月,b1.大区,b1.省份,sum(发货金额) as 发货金额,sum(回款金额) as 回款金额
from
(select year(dis.dDate) as 年,MONTH(dis.dDate) as 月,dep.cDepName as 大区,cusc1.cCCName as 省份,
sum(case when invc.cinvCCode in ('***') then diss.iSum end) as 发货金额,
0 as 回款金额
from DispatchList dis
left join DispatchLists diss on diss.DLID = dis.DLID
left join Customer cust on dis.cCusCode = cust.cCusCode
left join Person psn on cust.cCusPPerson = psn.cPersonCode
left join Inventory inv on diss.cinvcode = inv.cInvCode
left join InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join CustomerClass cusc on cust.cCCCode = cusc.cCCCode
left join CustomerClass cusc1 on left(cusc.cCCCode,2) = cusc1.cCCCode
left join Department dep on cust.cCusDepart = dep.cDepCode
where
--审核状态
dis.iverifystate = 2
--销售类型
and dis.cSTCode = 某销售类型
and cust.cCCCode between '***' and '***'
group by MONTH(dis.dDate),year(dis.dDate),dep.cDepName,cusc1.cCCName
union
select
year(dbill_date) as 年,MONTH(dbill_date) as 月,dep.cDepName as 大区,
cusc1.cCCName as 省份,0 as 发货金额,sum(mc) as 回款金额
from GL_accvouch ga
left join customer cust on ga.ccus_id = cust.cCusCode
left join CustomerClass cusc on cust.cCCCode = cusc.cCCCode
left join CustomerClass cusc1 on left(cusc.cCCCode,2) = cusc1.cCCCode
left join Department dep on cust.cCusDepart = dep.cDepCode
where ga.ccode = '***'
and ga.mc != 0
and cust.cCCCode between '***' and '***'
group by MONTH(dbill_date),year(dbill_date),dep.cDepName,cusc1.cCCName
) b1
group by b1.年,b1.月,b1.大区,b1.省份
) b on b.省份 = a.省份 and b.大区 = a.大区 and b.月 = a.月 and b.年 = a.年
where (发货金额 <> 0 or 回款金额 <> 0)
group by a.年,a.月,a.大区,a.省区经理,a.省份