库龄计算，SQL怎么写

[ldf0]

现有一表记录产品进出库
product_id(产品代码) inoutdate（出入库日期） qty（数量）
001 2004-01-01 10
001 2004-01-03 -8
001 2004-01-04 -1
001 2004-01-05 5
001 2004-01-07 -6
其中数量为正表示入库，数量为负表示出库，现要计算任意日期时库存产品的库存天数。系统默认先进先出。
比如查询2004-01-02日库龄，则为10个、库龄为1天
查询2004-01-03日库龄，则为2个、库龄为2天
查询2004-01-04日库龄，则为1个、库龄为3天
查询2004-01-05日库龄，则为1个、库龄为4天和5个、库龄为0天
查询2004-01-06日库龄，则为1个、库龄为5天和5个、库龄为1天
查询2004-01-07日库龄，则为0个、库龄为0

[prcgolf]

UP

[victorycyz]

declare @dt datetime,@pid char(3)
set @dt='2004-01-06'
set @pid='001'

declare @p table ( pid char(3),iodt datetime, qty int)

insert @p select '001', '2004-01-01', 10
union select '001', '2004-01-03', -8
union select '001', '2004-01-04', -1
union select '001', '2004-01-05', 5
union select '001', '2004-01-07', -6
union select '002', '2004-01-06', 20

select *
from ( select pid,
datediff(day,iodt,@dt) as 库龄,
qty+( select isnull(sum(qty),0)
from @p
where pid=@pid and iodt>a.iodt and iodt<=@dt and qty<0
)
as 数量
from @p a
where pid=@pid and iodt<=@dt
) b
where 数量>0

[ldf0]

多谢victorycyz(中海)兄，您的SQL基本可以了，但有一种情况数据不对，就是上一次入库数量还没出完，这次又入库，然后再出库一部分，这时候计算不对。比如下列数据，查询2004-01-08日库龄，按您的SQL计算是数量为2，库龄为3天，正确应当是数量为3，库龄为3天。

product_id(产品代码) inoutdate（出入库日期） qty（数量）
001 2004-01-01 10
001 2004-01-03 -8
001 2004-01-04 -1
001 2004-01-05 5
001 2004-01-07 -3

该怎么改进，还请指点，谢谢！

[victorycyz]

select pid,库龄,case when qty>数量 then 数量 else qty end as 数量
from ( select pid,
datediff(day,iodt,@dt) as 库龄,
qty,
( select isnull(sum(qty),0)
from @p
where pid=@pid and iodt<=@dt and (iodt<=a.iodt or (iodt>a.iodt and qty<0))
)
as 数量
from @p a
where pid=@pid and iodt<=@dt and qty>0
) b
where 数量>0

[zilang]

为什么同一笔货物001，在2004-01-06查询时有两笔结果啊，
学习中

[zilang]

哦，知道了：）

[JackSonzhang]

up

[ldf0]

问题解决了，谢谢 victorycyz(中海) 兄！！！

[ghosthjt]

我这个方法绝对正确的：

declare @dt datetime,@pid char(3)
set @dt='2004-01-08'
set @pid='001'

declare @p table ( pid char(3),iodt datetime, qty int)
insert @p select '001', '2004-01-01', 10
union select '001', '2004-01-03', -8
union select '001', '2004-01-04', -1
union select '001', '2004-01-05', 5
union select '001', '2004-01-07', -3

select t1.pid,datediff(day,t1.iodt,@dt) as 库龄,qty as 库存 from @p t1
cross join (
select top 1 pid,iodt,inqty+outqty 库存
from (
select t1.pid,t1.iodt,
(select sum(qty) from @p where iodt<=t1.iodt and qty>0) as inqty
from @p t1
where iodt<=@dt and qty>0
) t1
cross join (select sum(qty) as outqty from @p where iodt<=@dt and qty<0 group by pid) t2
where inqty+outqty>0
order by iodt
) t2
where qty>0 and t1.iodt>t2.iodt
union (

select top 1 pid,datediff(day,iodt,@dt),inqty+outqty 库存
from (
select t1.pid,t1.iodt,
(select sum(qty) from @p where iodt<=t1.iodt and qty>0) as inqty
from @p t1
where iodt<=@dt and qty>0
) t1
cross join (select sum(qty) as outqty from @p where iodt<=@dt and qty<0 group by pid) t2
where inqty+outqty>0
)