how to find number of days between successive visists?

Question

Post reply

how to find number of days between successive visists?

SQL-Learner

SSCommitted

Points: 1537
More actions
June 28, 2016 at 3:06 pm

#319597

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63482 More actions · Answer 1

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;

SQL-Learner SSCommitted Points: 1537 More actions · Answer 2

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

pietlinden SSC Guru Points: 63482 More actions · Answer 3

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

HappyGeek SSCoach Points: 18729 More actions · Answer 4

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.

...

drew.allen SSC Guru Points: 76984 More actions · Answer 5

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
Business Intelligence Analyst
Philadelphia, PA

HappyGeek SSCoach Points: 18729 More actions · Answer 6

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.

...

drew.allen SSC Guru Points: 76984 More actions · Answer 7

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
Business Intelligence Analyst
Philadelphia, PA

9423t8 Mr or Mrs. 500 Points: 507 More actions · Answer 8

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 … 😛

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 9

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

pietlinden SSC Guru Points: 63482 More actions · Answer 10

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

drew.allen SSC Guru Points: 76984 More actions · Answer 11

cad.delworth (7/12/2016)
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
Business Intelligence Analyst
Philadelphia, PA