用档案引导多个子表可以用会计期间档案

用档案引导多个子表可以用会计期间档案弥补其他档案没有时间的问题，这样可以避免大规模并表

--产品分析

select a.年,
大类编码,存货大类,分类编码,存货分类,a.存货编码,存货名称,规格型号,
主计量单位编码,计量单位名称,净重,毛重,sum(sr.销售数量) as 销售数量,sum(sr.销售收入) as 销售收入,
sum(qt.其他数量) as 其他数量,sum(cb.发出数量) as 发出数量,sum(cb.发出金额) as 发出金额
from
(select ua.iId as 月,ua.iYear 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 u8.UFDATA_账套文件夹.dbo.Inventory inv
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
cross join u8.UFSystem.dbo.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 u8.UFDATA_账套文件夹.dbo.DispatchList dis
left join u8.UFDATA_账套文件夹.dbo.DispatchLists diss on diss.DLID = dis.DLID
where
--审核状态
dis.iverifystate = 2
--销售类型
and ia.cSTCode = '销售类型'
and diss.citemname = '销售属性'
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 u8.UFDATA_账套文件夹.dbo.DispatchList dis
left join u8.UFDATA_账套文件夹.dbo.DispatchLists diss on diss.DLID = dis.DLID
where
--审核状态
dis.iverifystate = 2
--销售类型
and ia.cSTCode = '销售类型'
and diss.citemname != '销售属性'
group by MONTH(dis.dDate),year(dis.dDate),diss.cinvcode
) qt on qt.存货编码 = a.存货编码 and qt.月 = a.月 and qt.年 = a.年
--发出
left join
(select month(ia.dVouDate) as 月,year(ia.dVouDate) as 年,ia.cInvCode as 存货编码,
sum(ia.iAOutQuantity) as 发出数量,sum(ia.iAOutPrice) as 发出金额
from u8.UFDATA_账套文件夹.dbo.IA_Subsidiary ia
where
--销售类型
ia.cSTCode = '销售类型'
and ia.cName = '销售属性'
group by month(ia.dVouDate),year(ia.dVouDate),ia.cInvCode
) cb on cb.存货编码 = a.存货编码 and cb.月 = a.月 and cb.年 = a.年
where (销售数量 <> 0 or 销售收入 <> 0 or 其他数量 <> 0 or 发出数量 <> 0 or 发出金额 <> 0)

group by a.年,
大类编码,存货大类,分类编码,存货分类,a.存货编码,存货名称,规格型号,主计量单位编码,计量单位名称,净重,毛重
order by 大类编码,分类编码,a.存货编码