CODEWITHSUNDEEP
sql-serverleft-join
Derek Tomes
Derek Tomes

Reputation: 4007

left join to previous record in same table based on datetime column

I have a table that contains details of when a Member has visited.

I want to do a left join to previous record in same table based on a datetime column

When I look at the visit data I also want to see when their previous visit was so I can calculate how long it was between visits. The VisitDateTime column is a datetime datatype. The table potentially has hundreds of millions of rows in it.

Example Data:

MemberID VisitDateTime
1        2016-01-01 10:00:00.000
2        2016-01-01 10:01:00.000
1        2016-01-01 11:00:00.000
2        2016-01-01 11:30:00.000
3        2016-01-01 11:45:00.000
1        2016-01-01 11:50:00.000

So I'm trying to join a table back onto itself, with the previous Visit.VisitDateTime for that Member displayed as a PreviousVisitDateTime column:

Desired Result:

MemberID VisitDateTime           PreviousVisitDateTime
1        2016-01-01 10:00:00.000 null
2        2016-01-01 10:01:00.000 null
1        2016-01-01 11:00:00.000 2016-01-01 10:00:00.000
2        2016-01-01 11:30:00.000 2016-01-01 10:01:00.000
3        2016-01-01 11:45:00.000 null
1        2016-01-01 11:50:00.000 2016-01-01 11:00:00.000

But I can't even imagine how I'd go about doing this.

Upvotes: 1

Views: 1425

Answers (2)

David Cram
David Cram

Reputation: 778

If you want to do it with a left join:

SELECT a.MemberID, a.Visitdatetime,MIN(b.visitdatetime)
FROM #Test a
LEFT JOIN #Test b on b.MemberID=a.MemberID and b.visitDateTime < a.visitDateTime
GROUP BY a.MemberID,a.VisitDateTime
ORDER BY a.visitdatetime

Upvotes: 1

Liesel
Liesel

Reputation: 2979

You dont specify which version of SQL Server, so for SQL Server 2012 onwards:

CREATE TABLE #Test (MemberID INT, VisitDateTime DATETIME);

INSERT INTO #Test(MemberID, VisitDateTime) VALUES
(1, '2016-01-01 10:00:00.000'),
(2, '2016-01-01 10:01:00.000'),
(1, '2016-01-01 11:00:00.000'),
(2, '2016-01-01 11:30:00.000'),
(3, '2016-01-01 11:45:00.000'),
(1, '2016-01-01 11:50:00.000') 

SELECT MemberID
      ,VisitDateTime,
      LAG(VisitDateTime) OVER (PARTITION BY MemberID ORDER BY VisitDateTime) PreviousVisit FROM #Test
      ORDER BY VisitDateTime;


  MemberID    VisitDateTime           PreviousVisit
----------- ----------------------- -----------------------
1           2016-01-01 10:00:00.000 NULL
2           2016-01-01 10:01:00.000 NULL
1           2016-01-01 11:00:00.000 2016-01-01 10:00:00.000
2           2016-01-01 11:30:00.000 2016-01-01 10:01:00.000
3           2016-01-01 11:45:00.000 NULL
1           2016-01-01 11:50:00.000 2016-01-01 11:00:00.000

And to simulate LAG in 2008, try Alternate of lead lag function in sql server 2008

Upvotes: 2

Related Questions