SQLTeam.com | Weblogs | Forums

Datediff - sql


#1

i have table with customer,date
i need to calculate the datediff to each Customerkey from the first date to the last date
fro eaxmaple

22/09/2015-11/08/2015 = 42 days
27/10/215-22/09/2015 = 35 days
.....
....
....

CustomerKey Date DateDiff
4796 11/08/2015 00:00 0
4796 22/09/2015 00:00 42
4796 27/10/2015 00:00 35
4796 03/11/2015 00:00 7
4796 08/12/2015 00:00 35
4796 12/01/2016 00:00 35
4796 19/01/2016 00:00 7
4796 23/02/2016 00:00 35
4796 05/04/2016 00:00 42
4796 10/05/2016 00:00 35
4796 17/05/2016 00:00 7


#2

Not sure I ahve understood what you want, but maybe this:

SELECT CustomerKey, DateDiff(Day, MIN(Date], MAX{Date)) as [DateDiff]
FROM   MyTable
GROUP BY CustomerKey
ORDER BY CustomerKey

#3

no, no, no

i will try again

id CustomerKey Date DateDiff
1 4796 11/08/2015 00:00 0
2 4796 22/09/2015 00:00 42
3 4796 27/10/2015 00:00 35
4 4796 03/11/2015 00:00 7

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


#4

Hello, you can try this..

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

#5

OK, now that makes sense.

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.