Find breaks and continuous date spans
-
I have a table which contains a variety of medical claims. I need to accurately find treatment episodes unique to an individual and find a first date of treatment and last date of treatment. However, claims can be continous- so I need to find first and last dates in those cases. An example of the data would be like:
Patient AdmitDate DCDate
A 1/1/2005 1/10/2005
A 3/1/2005 3/10/2005
A 3/11/2005 3/20/2005
A 3/21/2005 3/25/2005
A 5/1/2005 6/1/2005
What I need to end up with is a results set that would look like:
Patient AdmitDate DCDate Days
A 1/1/2005 1/10/2005 10
A 3/1/2005 3/25/2005 24
A 5/1/2005 6/1/2005 30
I've tried a number of approaches with building a temp table and adding an identity field to assist, but end up pulling back the wrong DC dates or mis-combining. Any help or pointers would be greatly appreciated. I can't help but think there is a simple solution, but I'm blocked!
JMG
-
This thread may help.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=181044
-
Thanks for the response, but it was not in line with what I needed to do. I ended up coming up with a very un-eloquent solution by applying a row-to-row argument regarding is the rows where contiguous or not, then recombining the various break outs back into a final table.
Still would like a cleaner solution.
JMG
-
Definitely not easy to solve. The following is not the exact solution because it handles overlapping spans in addition to consecutive spans. Note that I modified the query for your data, but the summary is still the wording from my solution that involved bookings in dorm rooms.
/*
summary of coelesce algorithm.
for each person...
** find bookings that don't start within
or the day after another booking.
i.e. the first booking in a group of
overlapping or consecutive bookings.
move_in for resultset is move_in from those bookings.
** for each of those bookings...
** find bookings that don't end within
or the day before another booking.
i.e. the last booking in a group of
overlapping or consecutive bookings.
include only bookings that end
after the resultset move_in.
move_out for the resultset is the
minimum move_out from those bookings.
[courtesy of "Isaac Blank" <izblank@yahoo.com> on 6/11/2001]
*/
create table #t (Patient char(1), AdmitDate datetime, DCDate datetime, id
int identity primary key)
insert #t (Patient, AdmitDate, DCDate) values ('A', '1/1/2005', '1/10/2005')
insert #t (Patient, AdmitDate, DCDate) values ('A', '3/1/2005', '3/10/2005')
insert #t (Patient, AdmitDate, DCDate) values ('A', '3/11/2005', '3/20/2005')
insert #t (Patient, AdmitDate, DCDate) values ('A', '3/21/2005', '3/25/2005')
insert #t (Patient, AdmitDate, DCDate) values ('A', '5/1/2005', '6/1/2005')
SELECT
mi.Patient,
mi.AdmitDate,
DCDate =
(
SELECT
min(mo.DCDate)
FROM
#t mo
WHERE
NOT EXISTS
(
SELECT *
FROM #t b2
WHERE (mo.Patient = b2.Patient)
AND (dateadd(day, 1, mo.DCDate)
BETWEEN b2.AdmitDate AND b2.DCDate)
)
AND (mi.Patient=mo.Patient)
AND (mi.AdmitDate < mo.DCDate)
)
FROM
#t mi
WHERE
NOT EXISTS
(
SELECT *
FROM #t b2
WHERE (mi.Patient = b2.Patient)
AND (dateadd(day, -1, mi.AdmitDate)
BETWEEN b2.AdmitDate AND b2.DCDate)
)
-
I don't understand how you can tell what is part of a continuous claim....
For example, what tells you that the claims made on 3/1/2005, 3/11/2005 and the 3/21/2005 are all part of one claim?
Thanks
-
ignore my last post, figured it out
-
This looks like it does the trick, and is so much cleaner than my approach ;-). Thanks for looking into it. I need to go and test against some other scenarios, but it looks like it's definitely on the right path. Thanks again.
JMG
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply