Identifying gaps between intervals

Question

Identifying gaps between intervals

thusi-886269

Old Hand

Points: 331
More actions
September 20, 2008 at 12:02 am

#193824

Hi All
I have a fairly complex problem I believe is going to require some good temporal SQL. I have a bunch of patient prescriptions, each having a certain start date and an end date. These prescription durations may or may not overlap. What I want to do is identify gaps >30 days where there is no medication. So for eg if I have:
|-----pr1----|
|---pr2---|
|-----pr3-----|
I want to identify the duration between end of pr1 and beginning of pr2 is this duration is >30 days.
In the prescriptions table I have: PatientId, PrescriptionDrug, StartDate, Duration, EndDate where EndDate is simply StartDate+Duration.
Hope someone can help. This is using SQL Server 2008 Express btw.
Thanks
ps: not sure if the spaces will show up right in the prescriptions I've drawn..but I've drawn a gap between end of pr1 and beginning of pr2, but an overlap between pr2 end and beginning of pr3

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

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

Hello and welcome aboard! Please take the time to look at the article connected to the link below my signature. It'll help folks like me help you faster in the future. Thanks.

Here's what I came up with for a prescription timeline for testing purposes...

1/1 1/31 3/15 4/15 5/15 6/15 7/18 8/18 8/20 9/20

|---P1---| |---P2---| |---P4---| |---P5---| |---P6---|

|---P3---|

4/13 5/15

Here's some code that set's up this test kinda like what you'll find in the article I told you about... if you create some code like this on future problems, you get tested, working answers a lot quicker...

CREATE TABLE #MyHead (P INT, StartDate DATETIME, EndDate DATETIME)

INSERT INTO #MyHead

(P, StartDate , EndDate)

SELECT 1 , '20080101', '20080131' UNION ALL

SELECT 2 , '20080315', '20080415' UNION ALL

SELECT 3 , '20080413', '20080515' UNION ALL

SELECT 4 , '20080515', '20080615' UNION ALL

SELECT 5 , '20080718', '20080818' UNION ALL

SELECT 6 , '20080820', '20080920'

And, here's my crack at the solution. I haven't done testing outside of the scenario above. C'mon back if you find a scenario where it doesn't work.

SELECT lo.EndDate,MIN(hi.StartDate) AS StartDate

FROM #MyHead lo

INNER JOIN #MyHead hi

ON lo.StartDate < hi.StartDate

AND lo.EndDate < hi.EndDate

GROUP BY lo.EndDate

HAVING DATEDIFF(dd,lo.EndDate,MIN(hi.StartDate))>30

Let me know if that's what you were looking for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thusi-886269 Old Hand Points: 331 More actions · Answer 2

Thanks a lot for your help Jeff. A slight problem is that this doesn't include patientId in the join and I need to know which patients had the lapse..but this is just a matter of including the id in the SELECT and GROUP BY clause. Other than that, yup..it works like a charm! Thanks 🙂

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

thusi (9/21/2008)
Thanks a lot for your help Jeff. A slight problem is that this doesn't include patientId in the join and I need to know which patients had the lapse..but this is just a matter of including the id in the SELECT and GROUP BY clause. Other than that, yup..it works like a charm! Thanks 🙂

Thanks Thusi, I appreciate the feedback.

Did you take a look at the link in my signature, by any chance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thusi-886269 Old Hand Points: 331 More actions · Answer 4

Yes I did. Will try to follow the guidelines next time I post something 🙂

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

Cool... thanks for the feedback. Were you able to sucessfully incorporate the patient ID or do you still need any help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thusi-886269 Old Hand Points: 331 More actions · Answer 6

Yup..I sucessfully incorporated the patient ID and stuff and got the results I need. Thanks for following-up Jeff

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 7

Very cool. Thanks for the feedback. If you get the chance and your not violating any company agreement, could you post your final query? It might help other folks answer similar problems in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thusi-886269 Old Hand Points: 331 More actions · Answer 8

This is what I did:

SELECT lo.ID, lo.EndDate, MIN(hi.StartDate) AS StartDate

FROM Prescriptions lo

INNER JOIN Prescriptions hi

ON lo.ID = hi.ID

AND lo.StartDate < hi.StartDate

AND lo.EndDate < hi.EndDate

GROUP BY lo.MMID, lo.EndDate

HAVING (lo.EndDate-MIN(hi.StartDate)) >30

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 9

Heh... looks pretty familiar, but I gotta ask... is the following a type-o?

GROUP BY lo.MMID

It's not in the Select list and I'm sure this will cause an error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thusi-886269 Old Hand Points: 331 More actions · Answer 10

ah yes, my actual id column name is MMID. Forgot to change it back to ID after copy-pasting the code! Sorry