总结许多时间间隔内的天数

I have the following table:

我有下表:

CREATE table DataDiff (CLI tinyint, id tinyint, date datetime, countable bit)
insert into DataDiff values (234,1,convert(datetime,'17/12/1997',103),1)
insert into DataDiff values (234,2,convert(datetime,'09/07/1998',103),0)
insert into DataDiff values (234,3,convert(datetime,'11/08/1998',103),1)
insert into DataDiff values (234,4,convert(datetime,'29/12/1998',103),0)
insert into DataDiff values (234,5,convert(datetime,'01/02/1999',103),1)
insert into DataDiff values (234,6,convert(datetime,'03/02/1999',103),0)
insert into DataDiff values (234,7,convert(datetime,'03/02/1999',103),1)
insert into DataDiff values (234,8,convert(datetime,'29/03/1999',103),0)
insert into DataDiff values (234,9,convert(datetime,'29/03/1999',103),1)
insert into DataDiff values (234,10,convert(datetime,'31/03/1999',103),0)

I am having a hard time rewriting, without RBAR, a UDF to count the days from ID=1 up to ID=2 then from ID=3 to ID=4 and so on, always starting the count when COUNTABLE=1 and stopping when COUNTABLE=0 and then return the sum for a given CLI.

我很难在没有RBAR的情况下重写UDF来计算从ID = 1到ID = 2然后从ID = 3到ID = 4的天数等等,总是在COUNTABLE = 1时启动计数并在停止时停止COUNTABLE = 0然后返回给定CLI的总和。

The length in days of the five intervals pictured is:

图中五个区间的天数为:

ID=2 - ID=1  = 204
ID=4 - ID=3  = 140
ID=6 - ID=5  = 2
ID=8 - ID=7  = 54
ID=10 -ID=9  = 2

for a total of 402 "countable" days from the total of 469 days between ID=1 and ID=10

从ID = 1和ID = 10之间的总共469天总计402个“可计数”天数

1 个解决方案

#1

Try:

select t1.cli ,
       sum(t1.countable  * 
           datediff(day, t1.[DATE], coalesce(t2.[DATE],getdate())) ) daycount
from DataDiff t1
left join DataDiff t2 on t1.cli = t2.cli and t1.id+1 = t2.id
group by t1.cli

(Assumes that if the last recorded date for the cli is countable, then it should be counted up to the current date.)

(假设如果cli的最后记录日期是可数的,那么它应该计算到当前日期。)