sql 分段统计

 

create table tmptable(rq datetime)goinsert tmptable values('2010.1.1')insert tmptable values('2010.1.2')insert tmptable values('2010.1.3')insert tmptable values('2010.1.6')insert tmptable values('2010.1.7')insert tmptable values('2010.1.10')insert tmptable values('2010.1.11')insert tmptable values('2010.1.12')insert tmptable values('2010.1.19')insert tmptable values('2010.1.20')insert tmptable values('2010.1.22')insert tmptable values('2010.1.23')insert tmptable values('2010.1.28')goDECLARE @tmp TABLE(id INT IDENTITY(1, 1),datatime DATETIME,flag INT) ;INSERT @tmp(datatime,flag)SELECT a.rq AS datatime ,CASE WHEN b.rq IS NULL THEN 0 ELSE 1 ENDFROM tmptable a LEFT JOIN tmptable b ON a.rq = DATEADD(d, 1, b.rq) ORDER BY a.rq ;WITH maco AS (SELECT ROW_NUMBER() OVER ( ORDER BY a.id DESC ) AS rowid , a.datatime AS begintime , b.datatime,DATEDIFF(d,a.datatime,b.datatime) AS num FROM @tmp a LEFT JOIN @tmp b ON a.id = b.id + 1 WHERE a.flag = 0) SELECT CONVERT(VARCHAR(10), a.begintime, 120) AS '本期起始日期' , CONVERT(VARCHAR(10), ISNULL(b.datatime, a.begintime), 120) AS '本期终止日期', DATEDIFF(d,a.begintime,ISNULL(b.datatime, a.begintime))+1 AS '持续天数', ABS(ISNULL(a.num,0)) AS '距上一期天数' FROM maco a LEFT JOIN maco b ON a.rowid = b.rowid + 1 ORDER BY a.begintime