连续天数及最大连续时间天数开始时间和结束时间问题
如上图，readinfo表中存着表的读数信息
想求出每块表计的最大连续为0的天数和起始终止时间
上面得出的结果为：
meter_code 连续为0最大开始时间 连续为0最大结束时间 天数
meter_00001314 2013-04-20 00:00:00.000 2013-04-23 00:00:00.000 4
数据库环境为sql 2000
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (meter_code nvarchar(28),read_date datetime,read_data int)
insert into [TB]
select 'meter_00001314','2013-04-20 00:00:00.000',0 union all
select 'meter_00001314','2013-04-21 00:00:00.000',0 union all
select 'meter_00001314','2013-04-22 00:00:00.000',0 union all
select 'meter_00001314','2013-04-23 00:00:00.000',0 union all
select 'meter_00001314','2013-04-24 00:00:00.000',0 union all
select 'meter_00001314','2013-04-25 00:00:00.000',13714 union all
select 'meter_00001314','2013-04-26 00:00:00.000',0 union all
select 'meter_00001314','2013-04-27 00:00:00.000',13773 union all
select 'meter_00001314','2013-04-28 00:00:00.000',14717 union all
select 'meter_00001314','2013-04-29 00:00:00.000',0 union all
select 'meter_00001315','2013-04-20 00:00:00.000',0 union all
select 'meter_00001315','2013-04-21 00:00:00.000',0 union all
select 'meter_00001315','2013-04-22 00:00:00.000',0 union all
select 'meter_00001315','2013-04-23 00:00:00.000',0 union all
select 'meter_00001315','2013-04-24 00:00:00.000',13664
select * from [TB]
SELECT meter_code ,
grp AS '连续为0最大开始时间' ,
grp1 AS ' 连续为0最大结束时间' ,
DATEDIFF(dd, grp, grp1) + 1 AS '天数'
FROM ( SELECT meter_code ,
( SELECT MIN(read_date)
FROM TB AS B
WHERE b.read_date >= a.read_date
AND B.meter_code = A.meter_code
AND A.read_data = 0
AND NOT EXISTS ( SELECT 1
FROM TB AS c
WHERE DATEDIFF(dd,
c.read_date,
b.read_date) = 1 )
AND b.meter_code = meter_code
) AS grp ,
( SELECT MAX(read_date)
FROM TB AS B
WHERE b.read_date >= a.read_date
AND B.meter_code = A.meter_code
AND B.read_data = 0
AND EXISTS ( SELECT 1
FROM TB AS c
WHERE DATEDIFF(dd, c.read_date,
b.read_date) = 1 )
AND b.meter_code = meter_code
) AS grp1
FROM TB AS A
) T
WHERE grp IS NOT NULL
AND grp1 IS NOT NULL
GROUP BY meter_code ,
grp ,
grp1
/*
meter_code连续为0最大开始时间 连续为0最大结束时间天数
meter_000013142013-04-20 00:00:00.0002013-04-29 00:00:00.00010
meter_000013152013-04-20 00:00:00.0002013-04-23 00:00:00.0004*/
[解决办法]
麻烦一点，但总归出结果了。
select * from [TB]
create table #t (id bigint identity (1,1),meter_code varchar(100),read_date datetime,rank_code int,rank_code1 int)
insert #t (meter_code,read_date)
select meter_code,read_date from tb where read_data=0 order by meter_code,read_date
update x set rank_code=(case when exists
(select id from #t t where t.meter_code=x.meter_code and t.read_date=dateadd(dd,-1,x.read_date) ) then 0 else 1 end )
from #t x
update #t set rank_code1=(select sum(rank_code) from #t t where t.id<=x.id) from #t x
select rank_code1 as No,meter_code,min(read_date) as From_date,max(read_date) as To_date,datediff(dd,min(read_date),max(read_date))+1 as days from #t group by rank_code1,meter_code order by rank_code1,meter_code,min(read_date)
drop table #t
[解决办法]
SELECT meter_code ,
grp AS '连续为0最大开始时间' ,
grp1 AS ' 连续为0最大结束时间' ,
DATEDIFF(dd, grp, grp1) + 1 AS '天数'
FROM ( SELECT meter_code ,
( SELECT MIN(read_date)
FROM TB AS B
WHERE b.read_date >= a.read_date
AND B.meter_code = A.meter_code
AND A.read_data = 0
AND NOT EXISTS ( SELECT 1
FROM TB AS c
WHERE DATEDIFF(dd,
c.read_date,
b.read_date) = 1 )
AND b.meter_code = meter_code
) AS grp ,
( SELECT MAX(read_date)
FROM TB AS B
WHERE b.read_date >= a.read_date
AND B.meter_code = A.meter_code
AND B.read_data = 0
AND EXISTS ( SELECT 1
FROM TB AS c
WHERE DATEDIFF(dd, c.read_date,
b.read_date) = 1
AND b.read_data = c.read_data) --这里填个条件即可
AND b.meter_code = meter_code
) AS grp1
FROM TB AS A
) T
WHERE grp IS NOT NULL
AND grp1 IS NOT NULL
GROUP BY meter_code ,
grp ,
grp1
/*
meter_code连续为0最大开始时间 连续为0最大结束时间天数
meter_000013142013-04-20 00:00:00.0002013-04-24 00:00:00.0005
meter_000013152013-04-20 00:00:00.0002013-04-23 00:00:00.0004*/