(出入库表) (商品编号) (出入库日期) (数量)
入库 123 2020-01-01 100
入库 234 2020-01-02 100
出库 123 2020-01-03 50
出库 234 2020-01-04 50
出库 123 2020-01-05 50
出库 234 2020-01-06 50
入库 123 2020-01-07 100
入库 234 2020-01-08 100
出库 123 2020-01-09 30
出库 234 2020-01-10 30
按截止指定时间搜索结果 2020-2-20
按先进先出原则
想要达成这样的效果
(商品编号) (库龄) (库存)
123 40 70
234 41 70
declare @dt datetime
set @dt='2020-1-20'
declare @t1 table ( pid char(18),datetime datetime, amount int)
insert @t1 select '123', '2020-01-01', 1000
union select '123', '2020-01-15', 1000
union select '234', '2020-01-01', 1000
union select '234', '2020-01-15', 1000
declare @t2 table ( pid char(18),datetime datetime, amount int)
insert @t2 select '123', '2020-01-01', 50
union select '123', '2020-01-02', 50
union select '123', '2020-01-05', 50
union select '234', '2020-01-01', 50
union select '234', '2020-01-02', 50
union select '234', '2020-01-05', 50
select t1.pid,DATEDIFF(day,t1.maxDate,@dt)+1 '库龄',(t1.totalNum - t2.totalNum) '库存' from
(select pid,max(datetime) maxDate,sum(amount) totalNum from @t1 where DATEDIFF(day,datetime,@dt)>=0 group by pid) t1
left join (select pid,sum(amount)totalNum from @t2 where DATEDIFF(day,datetime,@dt)>=0 group by pid) t2 on t1.pid=t2.pid
按你的还是不正确
应该的到的是
123 20 1850
234 20 1850
结果库龄是 6 就是第二次进货的库龄了, 实际是第一次没销售完,就库龄就还是第一次的进货时间