SQL continuous date is a classic problem

Create Table Tmptable (RQ Datetime )
Go
Insert 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 ' )
Go
-- -Expected results
-- Start date of the current period end date duration from the previous period
-- 2010.1.1 2010.1.3 3 0
-- 2010.1.6 2010.1.7 2 3
-- 2010.1.10 2010.1.12 3 3
-- 2010.1.19 2010.1.20 2 7
-- 2010.1.22 2010.1.23 2
-- 2010.1.28 2010.1.28 1 5

Drop Table Tmptable
Go

Select Start date of current period = Min (RQ), end date of the current period = Max (RQ), duration = Max (Id1) - Min (Id1) + 1 , Days from the previous period = Case A. id1 - A. Id2 When - 1 Then 0 Else Max ( Datediff (D, rq2, rq )) End
From (
Select Id1 = Datediff (D, ' 2010-01-01 ' , Rq), Id2 = ( Select Count ( 1 ) From Tmptable Where RQ <= A. RQ), rq2 = ( Select Max (RQ) From Tmptable Where RQ < A. RQ ), * From Tmptable
)
Group By A. id1 - A. Id2
/*
Start date of the current period end date duration from the previous period
--------------------------------------------------------------------
00:00:00. 000 00:00:00. 000 3 0
2010-01-06 00:00:00. 000 2010-01-07 00:00:00. 000 2 3
2010-01-10 00:00:00. 000 2010-01-12 00:00:00. 000 3 3
2010-01-19 00:00:00. 000 2010-01-20 00:00:00. 000 2 7
2010-01-22 00:00:00. 000 2010-01-23 00:00:00. 000 2 2
2010-01-28 00:00:00. 000 2010-01-28 00:00:00. 000 1 5
Warning null values are eliminated for aggregation or other set operations.

(6 rows affected)
*/

http://topic.csdn.net/u/20110120/16/8cf92780-1a60-4a5a-94b1-15a468f738d9.html