[SQL ] 算出每筆日期間隔

[Severine (賽非茵)]

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)

========================================================

  請問該如何修改呢? 謝謝 ><

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 59.120.142.132
※ 文章網址: http://www.ptt.cc/bbs/Database/M.1404725459.A.328.html
→ scpisces:DATEADD(d,1,T2.Date)=>日期會變為 2/2,2/13,2/14,2/25
→ scpisces:只能算出2/13那一筆
推 scpisces:我的作法是給每一筆資料流水號
→ scpisces:也就是再多一個欄位Seq
→ scpisces:然後再用 LEFT JOIN @Temp AS T2 ON T1.Seq = T2.Seq+1
→ Severine:我研究看看 為了這個問題苦惱了一整天..謝謝!
→ Severine:現在想想流水號的概念不錯 我真笨居然沒想到T.T