收入重量汇总

--收入重量汇总

select sz.月 as 月,sz.年 as 年,sz.产品系列 as 产品系列,
sum(isnull(sz.销售收入,'0')+isnull(s.销售收入,'0'))as 销售收入,sum(sz.全部重量) as 全部重量
from
(select a.月 as 月,a.年 as 年,a.产品系列 as 产品系列,sum(sr.销售收入) as 销售收入,
sum(isnull(qt.全部数量,'0')*isnull(净重,'0')) as 全部重量
from
(select ua.iId as 月,ua.iYear as 年,
(select case when invc2.cInvCCode = '***' and inv.cInvName like '%***%' then '***'
else '其他产品' end ) as 产品系列,
invc2.cInvCCode as 大类编码,invc2.cInvCName as 存货大类,invc.cInvCCode as 分类编码,
invc.cInvCName as 存货分类,inv.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,inv.iInvWeight as 净重,inv.fGrossW as 毛重
from Inventory inv
left join InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
cross join UA_Period ua
where invc1.cInvCCode = '产品一类'
and ua.cAcc_Id ='账套号'
) a
--收入
left join(
select
MONTH(dis.dDate) as 月,year(dis.dDate) as 年,diss.cinvcode as 存货编码
,sum(diss.iQuantity) as 销售数量,sum(diss.iSum) as 销售收入
from DispatchList dis
left join DispatchLists diss on diss.DLID = dis.DLID
where ((dis.cSTCode in ('销售类型')) or (dis.dDate >= '特殊时间' and dis.cSTCode = '销售类型' ))
and dis.cDLCode != '特殊单号'
group by MONTH(dis.dDate),year(dis.dDate),diss.cinvcode
) sr on sr.存货编码 = a.存货编码 and sr.月 = a.月 and sr.年 = a.年
--全部数量
left join(
select
MONTH(dis.dDate) as 月,year(dis.dDate) as 年,diss.cinvcode as 存货编码
,sum(diss.iQuantity) as 全部数量
from DispatchList dis
left join DispatchLists diss on diss.DLID = dis.DLID
where dis.cSTCode != '销售类型'
and dis.cSTCode != '销售类型'
group by MONTH(dis.dDate),year(dis.dDate),diss.cinvcode
) qt on qt.存货编码 = a.存货编码 and qt.月 = a.月 and qt.年 = a.年
group by a.月,a.年,a.产品系列
) sz
--发货单外收入
left join(
select
MONTH(dbill_date) as 月,year(dbill_date) as 年,
(select case when ga.ccode = '***' then '***'
else '其他产品' end) as 产品系列,
sum(ga.mc) as 销售收入
from GL_accvouch ga
left join customer cust on ga.ccus_id = cust.cCusCode
where ga.ccode like '主营业务收入%'
and ga.mc != 0
and (ga.coutbillsign != '外部凭证单据类型'
or ga.coutbillsign IS NULL)
group by MONTH(dbill_date),year(dbill_date),
case when ga.ccode = '***' then '***'
else '其他产品' end
)s on s.产品系列 = sz.产品系列 and s.月 = sz.月 and s.年 = sz.年
group by sz.月,sz.年,sz.产品系列