DB: MS SQL
估狗了很久 剛剛也在本版搜尋了一下類似問題 找不太到..
只好發文問一下!
我想要取得每筆紀錄的時間間隔 請問該怎麼做呢?
=========================================================
問完以後改寫了一個blueshop的範例
DECLARE @Temp table (Date datetime, Orders int)
INSERT INTO @Temp VALUES ('2012/2/1',1000)
INSERT INTO @Temp VALUES ('2012/2/2',800)
INSERT INTO @Temp VALUES ('2012/2/3',3500)
INSERT INTO @Temp VALUES ('2012/2/4',4000)
SELECT
T1.Date ,
ISNULL(datediff(day,T2.Date ,T1.Date),null) AS daydiff
FROM @Temp AS T1
LEFT JOIN @Temp AS T2 ON T1.Date = DATEADD(d,1,T2.Date)
===============以上可以算出後四筆的相差時間==============
但是以下就不行,想必是對left join用法的理解錯誤
DECLARE @Temp table (Date datetime, Orders int)
INSERT INTO @Temp VALUES ('2012/2/1',1000)
INSERT INTO @Temp VALUES ('2012/2/12',800) <=修改過
INSERT INTO @Temp VALUES ('2012/2/13',3500) <=
INSERT INTO @Temp VALUES ('2012/2/24',4000) <=
SELECT
T1.Date ,
ISNULL(datediff(day,T2.Date ,T1.Date),null) AS daydiff
FROM @Temp AS T1
LEFT JOIN @Temp AS T2 ON T1.Date = DATEADD(d,1,T2.Date)
========================================================
請問該如何修改呢? 謝謝 ><

DATEADD(d,1,T2.Date)=>日期會變為 2/2,2/13,2/14,2/25只能算出2/13那一筆我的作法是給每一筆資料流水號也就是再多一個欄位Seq然後再用 LEFT JOIN @Temp AS T2 ON T1.Seq = T2.Seq+1

我研究看看 為了這個問題苦惱了一整天..謝謝!現在想想流水號的概念不錯 我真笨居然沒想到T.T