i want to find the the diff of days between the id and id +1
for exmaple the diff days for id=2 is :
date(id=2) - date(id=1) = 22/09/2015 - 11/08/2015 = 42 days
date(id=3) - date(id=2) = 27/10/2015 - 22/09/2015 = 35 days
WITH dateDiff AS
(
SELECT date1, ROW_NUMBER() OVER (ORDER BY date1) AS rowNum
FROM Table1
)
SELECT DATEDIFF(day, d1.date1, d2.Date1) AS dtDiff
FROM dateDiff d1
INNER JOIN dateDiff d2 ON
d1.rowNum = d2.rowNum - 1
Chriz suggestions looks good. Beware that won't select the 1st row for a given CustomerKey, you'll need an Outer Join for that. I expect you will also want to add CustomerKey to Chriz's SELECT statements.