料件库存表如下:
item_no yyyymm qcsl
AAA 200310 150
AAA 200311 50
AAA 200312 100料件异动表如下:
item_no vch_dt qty io
AAA 2003-10-01 10 1
AAA 2003-10-25 -110 -1
AAA 2003-11-05 30 1
AAA 2003-11-25 20 1我要验证AAA材料200311的期初50等于10月期初150+入库(10)-出库(110)
以此类推,要求推算当年的所有月份
item_no yyyymm qcsl
AAA 200310 150
AAA 200311 50
AAA 200312 100料件异动表如下:
item_no vch_dt qty io
AAA 2003-10-01 10 1
AAA 2003-10-25 -110 -1
AAA 2003-11-05 30 1
AAA 2003-11-25 20 1我要验证AAA材料200311的期初50等于10月期初150+入库(10)-出库(110)
以此类推,要求推算当年的所有月份
(select sum(qty) qty,item_no,convert(varchar(7),vch_dat) dt from 料件异动表
group by item_no,dt) b on (left(a.yyyymm,4)+'-'+right(a.yyyymm,2))=b.dt
(select sum(qty) qty,item_no,convert(varchar(7),vch_dat) dt from 料件异动表
group by item_no,dt) b on (left(a.yyyymm,4)+'-'+right(a.yyyymm,2))=b.dt
sum(io*qty) qty 这样才会减去出库的,这样应该能够算出每个月的月末库存。
但运行效率好像不高(非常慢),另外,好像这个语句还没有考虑期末库存是否月下个月的月初库存相等的比较。
select a.item_no,a.yyyymm,(a.qty+b.qty) total from 料件库存表 a inner join
(select sum(io*qty) qty,item_no,convert(varchar(7),vch_dt,20) dt from 料件异动表 group by item_no,convert(varchar(7),vch_dt,20)) b
on (left(a.yyyymm,4)+'-'+right(a.yyyymm,2))=b.dt
,库存量=a.qcsl
,计算量=b.结余
,差额=a.qcsl-b.结余
from 料件库存表 a join(
select item_no=isnull(a.item_no,b.item_no)
,年月=isnull(a.yyyymm,b.年月)
,结余=isnull(a.qcsl,0)+isnull(b.本期结余,0)
from 料件库存表 a full join(
select item_no,年月=convert(varchar(6),vch_dt,112),本期结余=sum(qty)
from 料件异动表
group by item_no,convert(varchar(6),vch_dt,112)
) b on a.item_no=b.item_no and a.yyyymm=b.年月
) b on a.item_no=b.item_no and datediff(month,b.年月+'01',a.yyyymm+'01')=1
select item_no=a.item_no,a.yyyymm
,库存量=a.qcsl
,计算量=b.结余
,差额=a.qcsl-b.结余
from 料件库存表 a join(
select item_no=isnull(a.item_no,b.item_no)
,年月=isnull(a.yyyymm,b.年月)
,结余=isnull(a.qcsl,0)+isnull(b.本期结余,0)
from 料件库存表 a full join(
select item_no,年月=convert(varchar(6),vch_dt,112),本期结余=sum(qty)
from 料件异动表
group by item_no,convert(varchar(6),vch_dt,112)
) b on a.item_no=b.item_no and a.yyyymm=b.年月
) b on a.item_no=b.item_no and datediff(month,b.年月+'01',a.yyyymm+'01')=1
order by case a.qcsl when b.结余 then 1 else 0 end,差额
declare @料件库存表 table(item_no varchar(3),yyyymm varchar(6),qcsl int)
insert into @料件库存表
select 'AAA','200310',150
union all select 'AAA','200311',50
union all select 'AAA','200312',100declare @料件异动表 table(item_no varchar(3),vch_dt datetime,qty int,io int)
insert into @料件异动表
select 'AAA','2003-10-01',10,1
union all select 'AAA','2003-10-25',-110,-1
union all select 'AAA','2003-11-05',30,1
union all select 'AAA','2003-11-25',20,1--验证查询
select item_no=a.item_no,a.yyyymm
,库存量=a.qcsl
,计算量=b.结余
,差额=a.qcsl-b.结余
from @料件库存表 a join(
select item_no=isnull(a.item_no,b.item_no)
,年月=isnull(a.yyyymm,b.年月)
,结余=isnull(a.qcsl,0)+isnull(b.本期结余,0)
from @料件库存表 a full join(
select item_no,年月=convert(varchar(6),vch_dt,112),本期结余=sum(qty)
from @料件异动表
group by item_no,convert(varchar(6),vch_dt,112)
) b on a.item_no=b.item_no and a.yyyymm=b.年月
) b on a.item_no=b.item_no and datediff(month,b.年月+'01',a.yyyymm+'01')=1
order by case a.qcsl when b.结余 then 1 else 0 end,差额/*--测试结果
item_no yyyymm 库存量 计算量 差额
------- ------ ----------- ----------- -----------
AAA 200311 50 50 0
AAA 200312 100 100 0(所影响的行数为 2 行)
--*/