May 18, 2005 at 2:46 pm
I'm trying to find a set-based way to find the intersection of two sets of date-ranges.
Our scenario: Home-care nurses are assigned responsibility for certain patients, for various lengths of time. While the patients are at home, the home-care nurse has primary responsibility for the patient. But when the patient is in the hospital, the hospital staff assume primary responsibilty.
Suppose we have a patient assigned to a nurse, and our historical log (below) shows that the nurse was assigned for exactly one year.
PatientID | NurseID | dtAssignedStart | dtAssignedEnd
-------------------------------------------------------------
123 | 789 | 01/01/2004 | 12/31/2004
-------------------------------------------------------------
During 2004, Patient #123 went to the hospital three times -- each time (how convenient!) for exactly one month.
PatientID | HospStayID | dtHosptart | dtHospEnd
-------------------------------------------------------------
123 | 777 | 02/01/2004 | 02/28/2004
-------------------------------------------------------------
123 | 778 | 06/01/2004 | 06/30/2004
-------------------------------------------------------------
123 | 779 | 11/01/2004 | 11/30/2004
-------------------------------------------------------------
So, what I need is a set-based way to "overlap" the one record from the first table on the three from the second table, and result in four records like this, showing when the nurse was effectively responsible for the patient.
PatientID | NurseID | dtRespStart | dtRespEnd
-------------------------------------------------------------
123 | 789 | 01/01/2004 | 01/31/2004
-------------------------------------------------------------
123 | 789 | 03/01/2004 | 03/31/2004
-------------------------------------------------------------
123 | 789 | 07/01/2004 | 10/31/2004
-------------------------------------------------------------
123 | 789 | 12/01/2004 | 12/31/2004
-------------------------------------------------------------
If you can help me accomplish this much, I will name my next child after you. I will write folks songs about you. They won't be good, but I'll do it. However...
If you want to really impress me (and save my bacon), I'd love to find a technique that handles less "neat" data. In the above example, the hospital stays are neatly bounded within the nurse's year of being assigned to the patient. Suppose we add two more stays, one overlapping Jan 1st and one Dec 31...
PatientID | HospStayID | dtHosptart | dtHospEnd
-------------------------------------------------------------
123 | 776 | 12/15/2003 | 01/15/2004
-------------------------------------------------------------
123 | 777 | 02/01/2004 | 02/28/2004
-------------------------------------------------------------
123 | 778 | 06/01/2004 | 06/30/2004
-------------------------------------------------------------
123 | 779 | 11/01/2004 | 11/30/2004
-------------------------------------------------------------
123 | 777 | 12/25/2004 | 01/15/2005
-------------------------------------------------------------
Then our result set ought to look like this...
PatientID | NurseID | dtRespStart | dtRespEnd
-------------------------------------------------------------
123 | 789 | 01/15/2004 | 01/31/2004
-------------------------------------------------------------
123 | 789 | 03/01/2004 | 03/31/2004
-------------------------------------------------------------
123 | 789 | 07/01/2004 | 10/31/2004
-------------------------------------------------------------
123 | 789 | 12/01/2004 | 12/25/2004
-------------------------------------------------------------
Suggestions? Insights? Black magic? All input welcome...
- Tom
May 18, 2005 at 6:14 pm
I've done this before. It is easiest if it's done in a multi statement proc. Looping is not necessary though, it's just easiest to stage in temp tables for joining to create the result set required.
Anyway, before I answer, tell me you don't want a singe SQL statement solution (it's possible, but gets really ugly looking).
May 18, 2005 at 7:49 pm
Finding overlaps is just a matter of looking for two date ranges where both start dates are earlier than both end dates.
So you can easily join a table with itself to see if there are any overlaps (different ID, but overlapping dates). So then you can have a function that translates these overlapping records into a new one, removing the old ones. No need for a cursor of course... just grab the earlier of the two start dates and the latter of the two end dates.
Be wary of the case where there are more than two overlapping dates. You may want to include another check in your where clause to see if there are any other overlapping dates.
Then run the query again, to see if there are now any overlaps. A while clause should do it for you.
I'll write some code for you soon... but I need to reboot now.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 18, 2005 at 8:50 pm
john,
multi-part solutions are fine. I'm an enthusiast for declaring table variables as I go and manipulating my data in stages. No need for one big honking single-stage rocket, if you prefer to tackle it in parts. I look forward to any suggested solution you can offer.
- Tom
May 18, 2005 at 8:57 pm
Rob,
I appreciate the post, but I'm afraid your answer is a bit too broad and conceptual for me to get much mileage from. You say "you can have a function that translates these overlapping record..." -- which is an enticing statement, certainly! -- but gets me no closer to actually writing that function.
Thanks for your offer to write some code. I don't expect anyone else to do the work for me, but a substantive pointer in the right direction would be tremendous help. Thanks in advance.
- Tom
May 18, 2005 at 9:14 pm
Tom,
I think I mis-read your question anyway... you're not really looking for overlaps - in fact, the hospital records won't overlap. You're looking for the gaps.
Try this (uncomment the lines for creating/populating the tables if you need to):
-- create table tblNurseAssigned
-- (patientid int, nurseid int, dtAssignedStart datetime, dtAssignedEnd datetime)
-- go
-- create table tblHospStay
-- (patientid int, hospstayid int, dtHospStart datetime, dtHospEnd datetime)
-- go
-- insert into tblNurseAssigned (PatientID, NurseID, dtAssignedStart, dtAssignedEnd)
-- values (123,789,'1-jan-2004','31-dec-2004')
-- go
-- insert into tblHospStay (patientid, hospstayid, dthospstart, dthospend)
-- values (123,776,'15-dec-2003','15-jan-2004')
-- insert into tblHospStay (patientid, hospstayid, dthospstart, dthospend)
-- values (123,777,'1-feb-2004','28-feb-2004')
-- insert into tblHospStay (patientid, hospstayid, dthospstart, dthospend)
-- values (123,778,'1-jun-2004','30-jun-2004')
-- insert into tblHospStay (patientid, hospstayid, dthospstart, dthospend)
-- values (123,779,'1-nov-2004','30-nov-2004')
-- insert into tblHospStay (patientid, hospstayid, dthospstart, dthospend)
-- values (123,777,'25-dec-2004','15-jan-2005')
-- select * from tblNurseAssigned
-- select * from tblHospStay
select n.patientid, n.nurseid, n.dtAssignedStart dtRespStart, dateadd(day,-1,sFirst.dtHospStart) dtRespEnd
from
tblNurseAssigned n
left join
tblHospStay s
on s.patientid = n.patientid
and s.dtHospStart < n.dtAssignedStart
and n.dtAssignedStart n.dtAssignedStart)
where s.patientid is null
union all
select n.patientid, n.nurseid, dateadd(day,1,s.dtHospEnd) dtRespStart, isnull(dateadd(day,-1,sNext.dtHospStart),n.dtAssignedEnd) dtRespEnd
from
tblNurseAssigned n
join
tblHospStay s
on s.patientid = n.patientid
and n.dtAssignedStart < s.dtHospEnd
and s.dtHospStart < n.dtAssignedEnd
left join
tblHospStay sNext
on sNext.patientid = n.patientid
and n.dtAssignedStart < sNext.dtHospEnd
and sNext.dtHospStart s.dtHospEnd)
where s.dtHospEnd < n.dtAssignedEnd
order by 1,2,3
The way this works is to first find the gap that appears before any hospital stays (if appropriate). Then grab all the rest of the gaps, looking at when the next hospital stay is after the one we're currently interested in.
Does this work for you?
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 19, 2005 at 10:47 am
Rob,
Thanks so much! That was a lot of work on my behalf! I will be running this shortly, and then comparing it to my live data to see what comes of it.
I do appreciate it.
- Tom
May 23, 2005 at 11:44 am
Okay, I'm a nimrod.
After putting all this up on a whiteboard to help me think it through, it occured to me that this is in fact a classic overlapping dates problem.
I was trying to take the nurse-assigned period, and shatter it into blocks based on the "in-hospital" time periods. But that means what I wanted was the overlapping blocks of time when a nurse was assigned and the patient was not in the hospital.
I have that data, so I have what I need. Then it was a simple matter of looking for overlapping blocks of time, and using those as my starting point.
Thanks all..
- Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply