create Table #Test (SN int, MyDay Date,Class int)
Insert into #test Values(
1,'2014/01/01',1),(
2,'2014/01/02',2),(
3,'2014/01/07',1),(
4,'2014/01/14',1),(
5,'2014/01/24',3),(
6,'2014/01/25',3),(
7,'2014/01/26',1),(
8,'2014/01/27',2)
;With cte as
(
Select *,ROW_NUMBER()Over(PARTITION by class order by Myday asc) Rn
From #test Where class=2
)
,Cte1
as
(
Select SN,Myday,Class,NULL as datedif,Rn From cte Where rn=1
Union All
Select B.SN,B.Myday,B.Class,DATEDIFF(day,A.Myday,b.Myday) ,B.Rn
From cte1 A
Inner join cte B on A.Rn = B.Rn-1
)
Select SN,Myday,Class,datedif From cte1
Drop table #test
==========================================================
SQL 2012以後的比較簡單
CREATE TABLE #Temp (SN INT, MyDay DATE, Class INT)
INSERT INTO #Temp
VALUES (1,'1/1/2014',1),
(1,'1/2/2014',2),
(3,'1/7/2014',1),
(4,'1/14/2014',1),
(5,'1/24/2014',3)
SELECT *,
DATEDIFF(D,LAG(MyDay) OVER (ORDER BY SN),MyDay) AS Diff
FROM #Temp
WHERE Class = 1
DROP TABLE #Temp