CODEWITHSUNDEEP
sqlsql-serverdatediffpartition-by
MrCPT
MrCPT

Reputation: 37

Date difference between rows by partition or level SQL

I am using Microsoft SQL Server. I have a table user ID's and login dates. I have the dates originally stored as Datetime, but I converted them to date. I need show the date difference in days between each login grouped by user ID; I want the function to not count the difference between logins if the user ID changes. An exammple of my table is:

user_id, login_date
356,2012-03-22
356,2012-03-22
356,2012-03-22
356,2012-03-23
356,2012-07-17
356,2012-07-19
356,2012-07-20
381,2011-11-28
473,2011-12-29
473,2011-12-29
473,2011-12-29
473,2011-12-29
473,2012-01-13
473,2012-01-26
473,2012-01-29

There are many similar posts, which I've tried to build my query off of. However, the datediff on my output doesn't seem to always match the dates. This is my latest attempt:

;with cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY login.user_id 
ORDER BY login.user_id, login.login_date) AS RN 
          FROM login)
SELECT a.user_id, a.login_date ,datediff(day, a.login_date, b.login_date)
FROM cte a
LEFT JOIN cte b
ON a.user_id = b.user_id
and a.rn = b.rn -1

A sample of the output is:

user_id, login_date
356,2012-03-22,0
356,2012-03-22,0
356,2012-03-22,0
356,2012-03-23,0
356,2012-07-17,1
356,2012-07-19,0
356,2012-07-20,0
381,2011-11-28,0
473,2011-12-29,0
473,2011-12-29,0
473,2011-12-29,0
473,2011-12-29,0
473,2012-01-13,11
473,2012-01-26,3
473,2012-01-29,0

I am not married to using a cte.

Upvotes: 1

Views: 11506

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use lag():

select cte.*,
       datediff(day,
                lag(login_date) over (partition by user_id order by login_date),
                login_date
               ) as diff
from cte;

Upvotes: 9

Related Questions