June 28, 2016 at 3:06 pm
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
June 28, 2016 at 3:17 pm
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;
June 28, 2016 at 3:26 pm
Thank You so much. It's working. Can you please explain the code how it works.
June 28, 2016 at 3:31 pm
Do you understand how windowing functions work? Read up on the LAG function. It will explain it all.
July 11, 2016 at 4:27 am
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.
...
July 11, 2016 at 1:06 pm
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
July 11, 2016 at 2:01 pm
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.
...
July 11, 2016 at 2:33 pm
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
July 12, 2016 at 2:18 am
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 … 😛
July 12, 2016 at 2:18 am
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
July 12, 2016 at 8:09 am
I agree, but then it's not my homework. If you want good help, provide create table scripts etc.
July 12, 2016 at 10:31 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply