sql 一个关于过期的问题

Question

create table #tb
(
date datetime,
num int
)insert into #tb
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-16',10 union all
select '2010-01-18',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10
----------------------------------
目的：
1.date 为时间，num为点数，正数代表积累，负数代表消费
2.点数有效期为为一固定值，举例为 10 天，如5号的点数，15号不可用
3.计算指定时间的可用点数如果统计2号的，那么2号的10点可以包含进去
代入数据测试：
2010-01-05，可用点数 30
2010-01-11，可用点数 0
2010-01-12，可用点数 0
2010-01-14，可用点数 10
2010-01-25，可用点数 20
----------------------------------
完成要求：
1.随意完成，只求结果。
2.上面两个字段为表必须字段，如果认为现有字段统计不方便，可自行设计表结构

Answer 1

create table #tb
(
date    datetime,
num        int
)insert into #tb
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-16',10 union all
select '2010-01-18',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10
SELECT DATE,(SELECT SUM(NUM) FROM #TB WHERE DATE<=T.DATE) FROM #TB T
（所影响的行数为 11 行）DATE                                                               
------------------------------------------------------ ----------- 
2010-01-01 00:00:00.000                                10
2010-01-02 00:00:00.000                                20
2010-01-05 00:00:00.000                                30
2010-01-10 00:00:00.000                                0
2010-01-14 00:00:00.000                                10
2010-01-16 00:00:00.000                                20
2010-01-18 00:00:00.000                                30
2010-01-21 00:00:00.000                                10
2010-01-23 00:00:00.000                                20
2010-01-26 00:00:00.000                                30
2010-01-27 00:00:00.000                                40（所影响的行数为 11 行）?

Answer 2

create table #tb
(
date    datetime,
num        int
)insert into #tb
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-16',10 union all
select '2010-01-18',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10DECLARE @MINTIME DATETIME,@MAXTIME DATETIMESELECT @MINTIME=MIN(DATE),@MAXTIME=MAX(DATE) FROM #TB--SELECT @MINTIME,@MAXTIME
SELECT 
DATE,
(SELECT SUM(ISNULL(NUM,0)) FROM #TB WHERE DATE<=T.DATE) 
FROM  (SELECT DATEADD(DD,NUMBER,CONVERT(VARCHAR(10),@MINTIME,120)) DATE
FROM MASTER..SPT_VALUES WHERE TYPE='P'
 AND DATEADD(DD,NUMBER,CONVERT(VARCHAR(10),@MINTIME,120))<=CONVERT(VARCHAR(10),@MAXTIME,120)) T
DATE                                                               
------------------------------------------------------ ----------- 
2010-01-01 00:00:00.000                                10
2010-01-02 00:00:00.000                                20
2010-01-03 00:00:00.000                                20
2010-01-04 00:00:00.000                                20
2010-01-05 00:00:00.000                                30
2010-01-06 00:00:00.000                                30
2010-01-07 00:00:00.000                                30
2010-01-08 00:00:00.000                                30
2010-01-09 00:00:00.000                                30
2010-01-10 00:00:00.000                                0
2010-01-11 00:00:00.000                                0
2010-01-12 00:00:00.000                                0
2010-01-13 00:00:00.000                                0
2010-01-14 00:00:00.000                                10
2010-01-15 00:00:00.000                                10
2010-01-16 00:00:00.000                                20
2010-01-17 00:00:00.000                                20
2010-01-18 00:00:00.000                                30
2010-01-19 00:00:00.000                                30
2010-01-20 00:00:00.000                                30
2010-01-21 00:00:00.000                                10
2010-01-22 00:00:00.000                                10
2010-01-23 00:00:00.000                                20
2010-01-24 00:00:00.000                                20
2010-01-25 00:00:00.000                                20
2010-01-26 00:00:00.000                                30
2010-01-27 00:00:00.000                                40（所影响的行数为 27 行）

Answer 3

参数为时间参数，要求的目的及注意地方如下：
1.不能简单用sum函数，因为要考虑过期的点数不统计
2.不能简单的忽略过期点数，除非能另外加一个字段做标记，如统计01-14的点数应该为10，如果忽略01，02过期的点数那么sum计算出来的是 -10
3.我自己认为需要增加字段菜方便统计，但是没想好。
4.别人问我，我问大家，一起分享，呵呵

Answer 4

2.不能简单的忽略过期点数，除非能另外加一个字段做标记，如统计01-14的点数应该为10，如果忽略01，02过期的点数那么sum计算出来的是 -10
3.我自己认为需要增加字段菜方便统计，但是没想好。
增加字段应该可以解决,你在条件那里限定为不忽略就行了,统计还是SUM来

Answer 5

这个如果引用我下面的数据，那么2010-01-27统计出来的40 就不对了
insert into #tb
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-15',10 union all
select '2010-01-16',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10

Answer 6

drop table #tb
create table #tb
(
date datetime,
num int
)insert into #tb
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-15',10 union all
select '2010-01-16',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10DECLARE @MINTIME DATETIME,@MAXTIME DATETIMESELECT @MINTIME=MIN(DATE),@MAXTIME=MAX(DATE) FROM #TB--SELECT @MINTIME,@MAXTIME
SELECT 
DATE,
(SELECT SUM(ISNULL(NUM,0)) FROM #TB WHERE DATE<=T.DATE) 
FROM  (SELECT DATEADD(DD,NUMBER,CONVERT(VARCHAR(10),@MINTIME,120)) DATE
FROM MASTER..SPT_VALUES WHERE TYPE='P'
 AND DATEADD(DD,NUMBER,CONVERT(VARCHAR(10),@MINTIME,120))<=CONVERT(VARCHAR(10),@MAXTIME,120)) T(11 行受影响)
DATE                    
----------------------- -----------
2010-01-01 00:00:00.000 10
2010-01-02 00:00:00.000 20
2010-01-03 00:00:00.000 20
2010-01-04 00:00:00.000 20
2010-01-05 00:00:00.000 30
2010-01-06 00:00:00.000 30
2010-01-07 00:00:00.000 30
2010-01-08 00:00:00.000 30
2010-01-09 00:00:00.000 30
2010-01-10 00:00:00.000 0
2010-01-11 00:00:00.000 0
2010-01-12 00:00:00.000 0
2010-01-13 00:00:00.000 0
2010-01-14 00:00:00.000 10
2010-01-15 00:00:00.000 20
2010-01-16 00:00:00.000 30
2010-01-17 00:00:00.000 30
2010-01-18 00:00:00.000 30
2010-01-19 00:00:00.000 30
2010-01-20 00:00:00.000 30
2010-01-21 00:00:00.000 10
2010-01-22 00:00:00.000 10
2010-01-23 00:00:00.000 20
2010-01-24 00:00:00.000 20
2010-01-25 00:00:00.000 20
2010-01-26 00:00:00.000 30
2010-01-27 00:00:00.000 40(27 行受影响)统计出来的40，但实际上应该是30，因为只能统计23，26，27的，16号那个已经过期了

Answer 7

create table #tb
(
date    datetime,
num        int
)
--多插入一点数据，以免片面性
insert into #tb  
select '2010-01-01',10 union all
select '2010-01-02',10 union all
select '2010-01-05',10 union all
select '2010-01-06',10 union all
select '2010-01-10',-30 union all
select '2010-01-14',10 union all
select '2010-01-15',10 union all
select '2010-01-16',10 union all
select '2010-01-18',10 union all
select '2010-01-21',-20 union all
select '2010-01-23',10 union all
select '2010-01-26',10 union all
select '2010-01-27',10
select a.date ,a.num ,b.num as num1,b.date as date1 into #c from (
select * from #tb where num >0) a  join (
select * from #tb where num <0) b on DATEDIFF (d,a.date,b.date) between 0 and 9
select *,ROW_NUMBER()over(partition by date1 order by date) tn into #d
from(
select * from #c where DATE in (
select MIN(date)  from #c where date1 is not null  group by date1)
union all
select date,Num,0,date1 from #c where DATE not in (
select MIN(date)  from #c where date1 is not null  group by date1)
 )a order by date;
 
 --上面语句要得出如下结果,意思是消费尽量扣日期靠前的分数
-- select * from #d
--    date  （积累日期）   num  num1    date1(消费日期）
  ----------------------   ---- --- ------------------------
--2010-01-01 00:00:00.000 10 -30 2010-01-10 00:00:00.000 1
--2010-01-02 00:00:00.000 10 0 2010-01-10 00:00:00.000 2
--2010-01-05 00:00:00.000 10 0 2010-01-10 00:00:00.000 3
--2010-01-06 00:00:00.000 10 0 2010-01-10 00:00:00.000 4
--2010-01-14 00:00:00.000 10 -20 2010-01-21 00:00:00.000 1
--2010-01-15 00:00:00.000 10 0 2010-01-21 00:00:00.000 2
--2010-01-16 00:00:00.000 10 0 2010-01-21 00:00:00.000 3
--2010-01-18 00:00:00.000 10 0 2010-01-21 00:00:00.000 4
with tb(date,num,total,isused,tn,date1)
as
(select date,num,Num+num1,1,tn,date1 from #d where tn=1
union all 
select a.date,a.num,(total+a.Num),case when(total+a.Num)<=0 then 1 else 0 end,a.tn ,a.date1
from #d a join tb b on a.tn=b.tn+1 and a.date1=b.date1
) 
select a.*,case when a.num<0 then 1 else b.isused end as isused into #e
  from #tb a left join tb b on a.date =b.date
  
  --上面语句要得出如下结果,1表示花费被扣分，0表示过期被扣分数
--  select * from #e
date                num isused
-------------------- --  -------
--2010-01-01 00:00:00.000 10 1
--2010-01-02 00:00:00.000 10 1
--2010-01-05 00:00:00.000 10 1
--2010-01-06 00:00:00.000 10 0
--2010-01-10 00:00:00.000 -30 1
--2010-01-14 00:00:00.000 10 1
--2010-01-15 00:00:00.000 10 1
--2010-01-16 00:00:00.000 10 0
--2010-01-18 00:00:00.000 10 0
--2010-01-21 00:00:00.000 -20 1
--2010-01-23 00:00:00.000 10 NULL
--2010-01-26 00:00:00.000 10 NULL
--2010-01-27 00:00:00.000 10 NULL
  
select SUM(case when datediff(d,date,'2010-1-23')>9 then  num*isnull(isused,0)  
else num  end) 可用分 from #e where DATEDIFF (d,date,'2010-1-23')>=0

--假设日期为2010-1-23，可用分等于所有23日之前的分数之和减去过期的分数
--示例数据 消费分和积累分成倍数关系,比较好处理,如果不成倍数关系,比如哪一天积累分为50分,则要另外考滤