根据输入的时间段与客户ID号,求一储存过程算出租用天数与合计数量

有一个数据表table
里边有字段[ID],[KFID],[TypeID](只有两个值1和2:1代表租出,2代表归还),[B1num](B1物品租用数量),[B2num](B2物品租用数量),[Cnum]](C物品租用数量),[Dnum]](D物品租用数量),[AddTime](借还操作时间)
[ID]  [KFID]  [TypeID]  [B1num] [B2num]  [Cnum]  [Dnum]      [AddTime]
1       2       1        10       10      10      10    2011-3-1  10:20:03
2       2       1         2       4       3       5     2011-3-12 10:20:03
3       2       1         1       1       1       1     2011-3-13 10:20:03
4       2       1         1       2       3       4     2011-3-15 10:20:03
5       2       1         2       5       3       5     2011-3-15 12:20:03
6       2       2         1       1       0       0     2011-3-29 10:20:03
7       2       1         0       0       0       10    2011-3-30 10:20:03
8       2       1         2       4       3       5     2011-4-2  10:20:03如数据表内容如上，根据客户KFID为2，时间段为2011-3-1到2011-3-31统计租借物品租用天数与数量,根据这个存储过程希望得到如下的数据结果B物品情况［B1+B2］
　时间　 　租出数量　　 归还数量 　 合计数量　  使用天数
2011-3-1     20            0          20          11     
2011-3-12    6             0          26          1
2011-3-13    2             0          28          2
2011-3-15    10            0          38          14           
2011-3-29    0             2          36          3
C物品情况［Cnum］
　时间　 　租出数量　　 归还数量 　 合计数量　  使用天数
2011-3-1     10            0          10          11     
2011-3-12    3             0          13          1
2011-3-13    1             0          14          2
2011-3-15    6             0          20          17      
D物品情况［Dnum］
　时间　 　租出数量　　 归还数量 　 合计数量　  使用天数
2011-3-1     10            0          10          11     
2011-3-12    5             0          15          1
2011-3-13    1             0          16          2
2011-3-15    9             0          25          17  
2011-3-30    10            0          35          2
求高手指点

解决方案 »

  1.   

    create table tb([ID] int,[KFID] int,[TypeID] int,[B1num] int,[B2num] int,[Cnum] int,[Dnum] int,[AddTime] datetime)
    insert into tb select 1,2,1,10,10,10,10,'2011-3-1 10:20:03'
    insert into tb select 2,2,1,2,4,3,5,'2011-3-12 10:20:03'
    insert into tb select 3,2,1,1,1,1,1,'2011-3-13 10:20:03'
    insert into tb select 4,2,1,1,2,3,4,'2011-3-15 10:20:03'
    insert into tb select 5,2,1,2,5,3,5,'2011-3-15 12:20:03'
    insert into tb select 6,2,2,1,1,0,0,'2011-3-29 10:20:03'
    insert into tb select 7,2,1,0,0,0,10,'2011-3-30 10:20:03'
    insert into tb select 8,2,1,2,4,3,5,'2011-4-2 10:20:03'
    go
    ;with cte as(
    select row_number()over(order by 时间)rn,* from(
    select convert(varchar(10),addtime,120)as 时间,
    sum(case when typeid=1 then b1num+b2num else 0 end)as 租出数量,
    sum(case when typeid=2 then b1num+b2num else 0 end)as 归还数量,
    sum(b1num+b2num) as 合计数量
    from tb
    where addtime between '2011-3-1' and '2011-3-31 23:59:59' and b1num+b2num>0
    group by convert(varchar(10),addtime,120)
    union all
    select '2011-04-01',0,0,0
    )t)
    select a.时间,a.租出数量,a.归还数量,a.合计数量,datediff(d,a.时间,b.时间)as 使用天数
    from cte a inner join cte b on a.rn=b.rn-1
    /*
    时间       租出数量    归还数量    合计数量    使用天数
    ---------- ----------- ----------- ----------- -----------
    2011-03-01 20          0           20          11
    2011-03-12 6           0           6           1
    2011-03-13 2           0           2           2
    2011-03-15 10          0           10          14
    2011-03-29 0           2           2           3(5 行受影响)
    */
    go
    drop table tb
      

  2.   

    c,d,参数处理(只要把上面b1num+b2num 改一下就行了).
      

  3.   

    首先很感谢你,有几点地方不明白,请指点下,学习理解下
    where addtime between '2011-3-1' and '2011-3-31 23:59:59' and b1num+b2num>0
    这句时间上加上23:59:59,为什么?
    select '2011-04-01',0,0,0  这里把2011-4-1这个加上 后边几个0是什么意思?
    c,d,参数处理参考这个再做成2个存储过程吗?有办法在一个存储过程里吗?
      

  4.   

    那3个0是补充的列值...要么列数不同不能union
      

  5.   

    查询的时间段是3月份,为什么要把"2011-4-1"union?
      

  6.   

    1.日期时间数据类型包含时间,如果仅 '2011-3-31'则会将这一天的数据忽略,因为'2011-3-31 10:10:10'>'2011-3-31'
    2.对于 b,原表有两列,要统计b1num+b2num,c和d原表中只有一列,只要用 Cnum 或 Dnum 代替 b1num+b2num 就行了,其他写法一样
    3.你要的结果里是处理两条记录的时间差,而最后一条的后面没有时间,我算了一下,似乎是将当天到月末一共几天,因此添加下月第一天作为下一条记录,仅用来与最后一条记录进行差减获得天数.
      

  7.   

    一直没明白你的意思,原来我看帖不仔细:
    create table tb([ID] int,[KFID] int,[TypeID] int,[B1num] int,[B2num] int,[Cnum] int,[Dnum] int,[AddTime] datetime)
    insert into tb select 1,2,1,10,10,10,10,'2011-3-1 10:20:03'
    insert into tb select 2,2,1,2,4,3,5,'2011-3-12 10:20:03'
    insert into tb select 3,2,1,1,1,1,1,'2011-3-13 10:20:03'
    insert into tb select 4,2,1,1,2,3,4,'2011-3-15 10:20:03'
    insert into tb select 5,2,1,2,5,3,5,'2011-3-15 12:20:03'
    insert into tb select 6,2,2,1,1,0,0,'2011-3-29 10:20:03'
    insert into tb select 7,2,1,0,0,0,10,'2011-3-30 10:20:03'
    insert into tb select 8,2,1,2,4,3,5,'2011-4-2 10:20:03'
    go
    ;with cte as(
    select row_number()over(order by 时间)rn,* from(
    select convert(varchar(10),addtime,120)as 时间,
    sum(case when typeid=1 then b1num+b2num else 0 end)as 租出数量,
    sum(case when typeid=2 then b1num+b2num else 0 end)as 归还数量,
    sum(b1num+b2num) as 合计数量
    from tb
    where addtime between '2011-3-1' and '2011-3-31 23:59:59' and b1num+b2num>0
    group by convert(varchar(10),addtime,120)
    union all
    select '2011-04-01',0,0,0
    )t)--select * from cte
    select a.时间,a.租出数量,a.归还数量,(select sum(合计数量) from cte where rn<=a.rn)as 合计数量,datediff(d,a.时间,b.时间)as 使用天数
    from cte a inner join cte b on a.rn=b.rn-1
    /*
    时间         租出数量        归还数量        合计数量        使用天数
    ---------- ----------- ----------- ----------- -----------
    2011-03-01 20          0           20          11
    2011-03-12 6           0           26          1
    2011-03-13 2           0           28          2
    2011-03-15 10          0           38          14
    2011-03-29 0           2           40          3(5 行受影响)*/
    go
    drop table tb
    但这样做效率不很高,如果数据量很大的话,要再加一个cte.
      

  8.   

    修正:
    create table tb([ID] int,[KFID] int,[TypeID] int,[B1num] int,[B2num] int,[Cnum] int,[Dnum] int,[AddTime] datetime)
    insert into tb select 1,2,1,10,10,10,10,'2011-3-1 10:20:03'
    insert into tb select 2,2,1,2,4,3,5,'2011-3-12 10:20:03'
    insert into tb select 3,2,1,1,1,1,1,'2011-3-13 10:20:03'
    insert into tb select 4,2,1,1,2,3,4,'2011-3-15 10:20:03'
    insert into tb select 5,2,1,2,5,3,5,'2011-3-15 12:20:03'
    insert into tb select 6,2,2,1,1,0,0,'2011-3-29 10:20:03'
    insert into tb select 7,2,1,0,0,0,10,'2011-3-30 10:20:03'
    insert into tb select 8,2,1,2,4,3,5,'2011-4-2 10:20:03'
    go
    ;with cte as(
    select row_number()over(order by 时间)rn,* from(
    select convert(varchar(10),addtime,120)as 时间,
    sum(case when typeid=1 then b1num+b2num else 0 end)as 租出数量,
    sum(case when typeid=2 then b1num+b2num else 0 end)as 归还数量,
    sum(case when typeid=1 then b1num+b2num else -b1num-b2num end) as 合计数量
    from tb
    where addtime between '2011-3-1' and '2011-3-31 23:59:59' and b1num+b2num>0
    group by convert(varchar(10),addtime,120)
    union all
    select '2011-04-01',0,0,0
    )t)
    select a.时间,a.租出数量,a.归还数量,(select sum(合计数量) from cte where rn<=a.rn) 合计数量,datediff(d,a.时间,b.时间)as 使用天数
    from cte a inner join cte b on a.rn=b.rn-1
    /*
    时间         租出数量        归还数量        合计数量        使用天数
    ---------- ----------- ----------- ----------- -----------
    2011-03-01 20          0           20          11
    2011-03-12 6           0           26          1
    2011-03-13 2           0           28          2
    2011-03-15 10          0           38          14
    2011-03-29 0           2           36          3(5 行受影响)*/
    go
    drop table tb