# how to find number of days between successive visists?

• PatientId PatientNO Initials---- Patients Table

1 1 ABC

2 3 DEF

3 4 HIJ

VisitId PatientID VisitNo VisitDate

1 1112-Jan -2005

21223-Jan-2005

31328-Jan-2005

42101-Feb-2005

52208-Feb-2005

63103-Feb-2005

73208-Feb-2005

Produce a report showing the number of days between successive visits. Leave the column blank for the first visit for each patient. The results should be as follows:

PatientNo VisitNo VisitDate DaysSincePrevious

1 112-Jan-2005

1223-Jan-200511

1328-Jan-20055

2101-Feb-2005

2208-Feb-20057

4103-Feb-2005

4208-Feb-20055

• Like this is one way:

`-- show number of days between successive visits`

`SELECT PatientID`

`,VisitID`

`,VisitNo`

`,VisitDate`

`,DATEDIFF(day,LAG(VisitDate,1) OVER (PARTITION BY PatientID ORDER BY VisitDate),VisitDate) AS Elapsed`

`FROM`

`(`

`SELECT 1 AS VisitID,1 AS PatientID,1 AS VisitNo,'12-Jan-2005' AS VisitDate`

`UNION ALL`

`SELECT 2,1,2,'23-Jan-2005'`

`UNION ALL`

`SELECT 3,1,3,'28-Jan-2005'`

`UNION ALL`

`SELECT 4,2,1,'01-Feb-2005'`

`UNION ALL`

`SELECT 5,2,2,'08-Feb-2005'`

`UNION ALL`

`SELECT 6,3,1,'03-Feb-2005'`

`UNION ALL`

`SELECT 7,3,2,'08-Feb-2005') visits;`

• Thank You so much. It's working. Can you please explain the code how it works.

• Do you understand how windowing functions work? Read up on the LAG function. It will explain it all.

• I felt it was a bit odd that patients seemed to only exist in isolation and that another wasn't seen until the previous had completed treatment (so to speak) so mixed things up a little. Based on what had been done I found the following worked:

`SELECT * INTO #Visit`

`FROM`

`(SELECT 1 AS VisitID, 1 AS PatientID, 1 AS VisitNo, '12-Jan-2005' AS VisitDate`

`UNION ALL`

`SELECT 2, 1, 2,'23-Jan-2005'`

`UNION ALL`

`SELECT 3, 3, 1, '28-Jan-2005'`

`UNION ALL`

`SELECT 4, 2, 1, '01-Feb-2005'`

`UNION ALL`

`SELECT 5, 2, 2, '08-Feb-2005'`

`UNION ALL`

`SELECT 6, 1, 3, '03-Feb-2005'`

`UNION ALL`

`SELECT 7, 1, 4, '08-Feb-2005')`

`as V`

`SELECT PatientID`

`,VisitID`

`,VisitNo`

`,VisitDate`

`, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo) PrevVisit`

`, Datediff(DAY, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo), VisitDate) DaysSincePV`

`FROM #Visit`

`ORDER BY PatientId, VisitNo`

`DROP TABLE #Visit`

I have included the PrevVisit for illustration purposes.

...

• HappyGeek (7/11/2016)

I felt it was a bit odd that patients seemed to only exist in isolation and that another wasn't seen until the previous had completed treatment (so to speak) so mixed things up a little. Based on what had been done I found the following worked:

`SELECT * INTO #Visit`

`FROM`

`(SELECT 1 AS VisitID, 1 AS PatientID, 1 AS VisitNo, '12-Jan-2005' AS VisitDate`

`UNION ALL`

`SELECT 2, 1, 2,'23-Jan-2005'`

`UNION ALL`

`SELECT 3, 3, 1, '28-Jan-2005'`

`UNION ALL`

`SELECT 4, 2, 1, '01-Feb-2005'`

`UNION ALL`

`SELECT 5, 2, 2, '08-Feb-2005'`

`UNION ALL`

`SELECT 6, 1, 3, '03-Feb-2005'`

`UNION ALL`

`SELECT 7, 1, 4, '08-Feb-2005')`

`as V`

`SELECT PatientID`

`,VisitID`

`,VisitNo`

`,VisitDate`

`, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo) PrevVisit`

`, Datediff(DAY, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo), VisitDate) DaysSincePV`

`FROM #Visit`

`ORDER BY PatientId, VisitNo`

`DROP TABLE #Visit`

I have included the PrevVisit for illustration purposes.

Since all records within a partition necessarily have the same value for each field in the PARTITION BY clause, there is no reason to include any of those fields in the ORDER BY clause. In other words, there is no reason to have the PatientId in both the PARTITION BY and ORDER BY clauses in this particular case.

Drew

J. Drew Allen

• drew.allen (7/11/2016)

Since all records within a partition necessarily have the same value for each field in the PARTITION BY clause, there is no reason to include any of those fields in the ORDER BY clause. In other words, there is no reason to have the PatientId in both the PARTITION BY and ORDER BY clauses in this particular case.

Drew

True, but since I started with this:

`SELECT PatientID`

`,VisitID`

`,VisitNo`

`,VisitDate`

`, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo) PrevVisit`

`, Datediff(DAY, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo), VisitDate) DaysSincePV`

`, DATEDIFF(day, LAG(VisitDate, 1) OVER (PARTITION BY PatientID ORDER BY VisitDate),VisitDate) AS Elapsed`

`FROM #Visit`

`ORDER BY PatientId, VisitNo`

It did make a difference , well at least it did to the results I got, so I left it in. Never realised it would cause offence. I will be more sensitive in future.

...

• HappyGeek (7/11/2016)

drew.allen (7/11/2016)

Since all records within a partition necessarily have the same value for each field in the PARTITION BY clause, there is no reason to include any of those fields in the ORDER BY clause. In other words, there is no reason to have the PatientId in both the PARTITION BY and ORDER BY clauses in this particular case.

Drew

True, but since I started with this:

`SELECT PatientID`

`,VisitID`

`,VisitNo`

`,VisitDate`

`, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo) PrevVisit`

`, Datediff(DAY, LAG(VisitDate, 1) OVER (PARTITION BY PatientId ORDER BY PatientId, VisitNo), VisitDate) DaysSincePV`

`, DATEDIFF(day, LAG(VisitDate, 1) OVER (PARTITION BY PatientID ORDER BY VisitDate),VisitDate) AS Elapsed`

`FROM #Visit`

`ORDER BY PatientId, VisitNo`

It did make a difference , well at least it did to the results I got, so I left it in. Never realised it would cause offence. I will be more sensitive in future.

I was simply pointing out that it was unnecessary, so that others would know that they didn't need to include it.

The reason that you were getting bad results is that your "dates" aren't really dates. They're strings, so they sort alphabetically. Since your "dates" are in DD-MMM-YYYY order, they sort by the day, then the month, then the year. That is part of the reason that I always use ANSI dates: even if they are strings, they will sort in the correct order: year, then month, then day.

Drew

J. Drew Allen

• drew.allen (7/11/2016)

The reason that you were getting bad results is that your "dates" aren't really dates. They're strings, so they sort alphabetically.

The OP didn't specify the data type of any columns: and given the sketchy description, we can't really assume anything.

The responders all chose to set up their temporary tables using quick'n'dirty string literals for the 'dates' (possibly taking their lead from the first responder?). To some, that will seem like a lazy way to set up a demo table, but then again: we are just doing someone's class assignment here … 😛

• `set dateformat dmy`

`declare @Visit table (`

`VisitId int not null,`

`PatientID int not null,`

`VisitNo int not null,`

`VisitDate datetime not null`

`)`

`insert into @Visit select 1, 1, 1, '12/01/2005'`

`insert into @Visit select 2, 1, 2, '23/01/2005'`

`insert into @Visit select 3, 1, 3, '28/01/2005'`

`insert into @Visit select 4, 2, 1, '01/02/2005'`

`insert into @Visit select 5, 2, 2, '08/02/2005'`

`insert into @Visit select 6, 3, 1, '03/02/2005'`

`insert into @Visit select 7, 3, 2, '08/02/2005'`

`select v.PatientID, v.VisitID, v.VisitDate,`

`datediff(dd, (select visitdate from @visit v2 where v2.PatientID = v.PatientID and v2.VisitNo = v.VisitNo - 1), v.VisitDate) as DaysSincePrevious`

`from @Visit v`

`order by PatientId, VisitNo`

This works with both examples above

• I agree, but then it's not my homework. If you want good help, provide create table scripts etc.

drew.allen (7/11/2016)

The reason that you were getting bad results is that your "dates" aren't really dates. They're strings, so they sort alphabetically.

The OP didn't specify the data type of any columns: and given the sketchy description, we can't really assume anything.

There aren't any assumptions here. In order for the LAG to operate correctly, the ORDER BY needs to be specified correctly. The post I responded to used a day-month abbreviation (English)-year order which is certainly not correct.

Drew

J. Drew Allen