求一条SQL语句，关于库龄的，在线等，急！！！

Question

先给大家看一下表：CREATE TABLE [dbo].[scm_costingbill](
[direction] [int] NULL, --出入库标识，1是入库，2是出库
[billdate] [datetime] NULL,--单据日期，入库日期和出库日期
[mtlid] [int] NULL,--物料的ID
[samount] [decimal](18, 4) NULL --数量，入库数量和出库数量。
) ON [PRIMARY]
数据：
insert into scm_costingbill (direction,billdate,mtlid,samount)
select 1,'2010-11-01',1,111 union all
select 1,'2010-11-02',2,121 union all
select 2,'2010-11-04',1,6 union all
select 2,'2010-11-05',2,34 问题描述：该表为库存表，查询剩余库存语句如下：select mtlid,sum(case when direction=1 then samount else -samount end) amount from scm_costingbill group by mtlid
结果为：
mtlid      amount（库存数量）
1 105.0000
2 87.0000需求：
需要查询物料在库存表中存放的天数和库存数量，就是当前日期减去入库日期。问题所在：因为入库日期和出库日期是不同的。
在线等高人，一旦解决，马上给分。

Answer 1

有批次還不好辦？按批次統計。如果批號帶上個日期就更好辦了，如批號格式: YYMMDDXXXX

Answer 2

应大家要求，补上数据：表：
CREATE TABLE [dbo].[scm_costingbill](
    [direction] [int] NULL, --出入库标识，1是入库，2是出库
    [billdate] [datetime] NULL,--单据日期，入库日期和出库日期
    [mtlid] [int] NULL,--物料的ID
    [samount] [decimal](18, 4) NULL --数量，入库数量和出库数量。
) ON [PRIMARY]
数据：
insert into scm_costingbill (direction,billdate,mtlid,samount,batchamount)
select 1,'2010-11-01',1,90,'1101001' union all
select 1,'2010-11-01',1,111,'1101002' union all
select 1,'2010-11-02',1,76,'1102001' union all
select 2,'2010-11-04',1,50,'1101001' union all
select 2,'2010-11-05',1,50,'1101002' union all
select 2,'2010-11-05',1,14,'1102001' union all
select 1,'2010-11-02',2,121,'1102001' union all
select 1,'2010-11-05',2,96,'1105001' union all
select 2,'2010-11-05',2,22,'1102001' union all
select 2,'2010-11-07',2,4,'1105001' union all
select 2,'2010-11-08',2,19,'1105001'批次的规则为日期+第几批如11月2日第二批：1102002
查询库存的语句为：select mtlid,sum(case when direction=1 then samount else -samount end) amount,batchamount from scm_costingbill
group by mtlid,batchamount求库龄SQL

Answer 3

batchamount列 在表里没有呀？ 应该就是samount 吧？ 

Answer 4

不好意思，真少了一列CREATE TABLE [dbo].[scm_costingbill](
    [direction] [int] NULL, --出入库标识，1是入库，2是出库
    [billdate] [datetime] NULL,--单据日期，入库日期和出库日期
    [mtlid] [int] NULL,--物料的ID
    [samount] [decimal](18, 4), NULL --数量，入库数量和出库数量。
     [batchamount] varchar(30) 
) ON [PRIMARY]

Answer 5

select 
DATEDIFF(day,min(billdate),getdate()) nday
,SUM(case when direction=1 then samount else -samount end) amount
,mtlid,batchamount
from scm_costingbill
group by mtlid,batchamount--结果
nday amount mtlid batchamount
31 40.0000 1 1101001
31 61.0000 1 1101002
30 62.0000 1 1102001
30 99.0000 2 1102001
27 73.0000 2 1105001结果不是你想要的！？你最好给个想要的结果，那样便于理解你的要求！

Answer 6

select 
    DATEDIFF(day,min(billdate),max(billdate)) nday
    ,SUM(case when direction=1 then samount else -samount end) amount
    ,mtlid,batchamount
from scm_costingbill
group by mtlid,batchamount

Answer 7

CREATE TABLE [dbo].[scm_costingbill](
    [direction] [int] NULL, --出入库标识，1是入库，2是出库
    [billdate] [datetime] NULL,--单据日期，入库日期和出库日期
    [mtlid] [int] NULL,--物料的ID
    [samount] [decimal](18, 4) NULL --数量，入库数量和出库数量。
) ON [PRIMARY]
go
insert into scm_costingbill (direction,billdate,mtlid,samount)
select 1,'2010-11-01',1,111 union all
select 1,'2010-11-02',2,121 union all
select 2,'2010-11-04',1,6 union all
select 2,'2010-11-05',2,34 union all
select 2,'2010-11-07',1,55 union all
select 2,'2010-11-07',2,72 union all
select 1,'2010-11-08',1,120 union all
select 1,'2010-11-12',2,150 union all
select 2,'2010-11-14',1,108 union all
select 2,'2010-11-16',1,55 union all
select 1,'2010-11-18',1,100
go
select row_number() over(partition by mtlid order by billdate)as rm,* into #1 from scm_costingbill where direction=1
select row_number() over(partition by mtlid order by billdate)as rm,* into #2 from scm_costingbill where direction=2
;with cte as(
select direction,billdate,mtlid,samount as leftamount,0 as flg,rm from #1 a where rm=1
union all
select a.direction,a.billdate,a.mtlid,convert(decimal(18,4),a.leftamount-b.samount) as leftamount,convert(int,b.rm) as flg,a.rm
from cte a inner join #2 b on a.mtlid=b.mtlid where a.leftamount>0 and b.rm=a.flg+1
union all
select a.direction,b.billdate,a.mtlid,convert(decimal(18,4),b.samount+a.leftamount) as leftamount,a.flg,b.rm
from cte a inner join #1 b on a.mtlid=b.mtlid where a.leftamount<0 and a.rm=b.rm-1
)--select * from cte order by mtlid,billdate
select mtlid,billdate,leftamount,datediff(dd,billdate,getdate())as days from cte a where flg=(select max(flg) from cte where mtlid=a.mtlid) and leftamount>0
union all
select mtlid,billdate,samount,datediff(dd,billdate,getdate()) from #1 a where rm>(select max(rm) from cte where mtlid=a.mtlid)
order by mtlid,billdate
/*
mtlid       billdate                leftamount                              days
----------- ----------------------- --------------------------------------- -----------
1           2010-11-08 00:00:00.000 7.0000                                  24
1           2010-11-18 00:00:00.000 100.0000                                14
2           2010-11-02 00:00:00.000 15.0000                                 30
2           2010-11-12 00:00:00.000 150.0000                                20(4 行受影响)*/
go
drop table scm_costingbill,#1,#2