Identifying durations between intervals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Identifying durations between intervals

Author

Topic

thusi
Starting Member

25 Posts

Posted - 2008-09-20 : 01:48:16

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

thusi
Starting Member

25 Posts

Posted - 2008-09-20 : 01:53:38

Oppe..sorry, cos of the formatting seems like all the spaces I had between my pr2 and pr3 have disappeared. What I had was a gap between the end of pr1 and beginning of pr2, but an overlap between pr2 and pr3. Hope that helps visualise my issue. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-20 : 02:02:42

May be this

SELECT p.PatientID,p.EndDate,q.StartDate
FROM Prescriptions p
CROSS APPLY(SELECT TOP 1 *
            FROM Prescriptions
            WHERE PatientId=p.PatientId
            AND DATEDIFF(dd,StartDate,p.EndDate)>30)q

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-20 : 05:27:30

It needs a little more than Visakh's code, since you've got to check that the end of the prescription that starts the gap is indeed the start of a gap.

This query doesn't attempt to show the cause of the gaps; it wasn't obvious how to break ties between prescriptions that end at the same time (and start the gap) or start at the same time (and end the gap).

The outermost select was really just to assure me that the gap was more than 30 days.

Performance on this query would probably benefit from an index on Prescriptions(PatientID, StartDate).


SELECT PatientID, GapStart, GapEnd, CAST(GapEnd - GapStart AS float) AS GapDays
FROM (
    SELECT DISTINCT S.PatientID, S.EndDate AS GapStart, E.StartDate AS GapEnd
    FROM Prescriptions AS S -- start of gap
    CROSS APPLY (
        SELECT TOP 1 *
        FROM Prescriptions AS E -- end of gap: next starting prescription
        WHERE E.PatientID = S.PatientID
          AND E.StartDate > S.EndDate
        ORDER BY E.StartDate ASC
      ) AS E
    WHERE NOT EXISTS (
        SELECT *
        FROM Prescriptions AS O -- gap starts when there are no extending overlaps
        WHERE O.PatientID = S.PatientID
          AND O.StartDate <= DATEADD(day, 30, S.EndDate)
          AND O.EndDate > S.EndDate
      )
  ) AS G
ORDER BY GapDays

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-20 : 06:24:09

Hmm... after a cup of coffee it becomes apparent to me that the two constraints can be tested together, so the NOT EXISTS isn't necessary:


SELECT PatientID, GapStart, GapEnd, CAST(GapEnd - GapStart AS float) AS GapDays
FROM (
    SELECT DISTINCT S.PatientID, S.EndDate AS GapStart, E.StartDate AS GapEnd
    FROM Prescriptions AS S -- start of gap
    CROSS APPLY (
        SELECT TOP 1 *
        FROM Prescriptions AS E -- end of gap: earliest starting prescription that ends after
        WHERE E.PatientID = S.PatientID
          AND E.EndDate > S.EndDate
        ORDER BY E.StartDate ASC
      ) AS E
    WHERE E.StartDate > DATEADD(day, 30, S.EndDate)
  ) AS G
ORDER BY GapDays

With this query, your index probably ought to include the EndDate, and even then it runs a little slower on my test data.

thusi
Starting Member

25 Posts

Posted - 2008-09-20 : 10:05:51

Wow..COOL. THANKS Arnold! Your 2nd query actually works pretty well. I still need to do some validation on the results however - for eg I wasn't sure why the 'ORDER BY E.StartDate ASC' line needs to be there..but if I comment it out, I get a different number of results as the answer. Also, I'm not sure if this takes into consideration any embedded scripts there might be. For eg, there could be data like:
10-Jan-08 to 25-Feb-08
15-Jan-08 to 15-Feb-08
2-Mar-08 to 10-Apr-08; in which case the lapse should be picked up as 25-Feb to 2-Mar. At any rate, thanks a lot :)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-20 : 12:34:53

I'll try to explain that second query.

Think about the CROSS APPLY from the point of view of a row in S. We're considering whether the end of prescription S represents the start of a gap in medication.

We're looking at the prescriptions for the patient that end after S ends because those either overlap the end of S or start after S has finished (I hope the assumption that all your prescriptions have non-negative duration is true!)

So of those prescriptions E is one that starts earliest -- that's what the TOP 1...ORDER BY E.StartDate ASC is doing. And it either started before S ended (in which case S isn't the start of a gap) or after a gap. But the gap has to be more than 30 days so we can test it with E.StartDate > DATEADD(day, 30, S.EndDate)

The query definitely should behave as you're expecting with the embedded script example you give. Though 25-Feb to 2-Mar isn't a long enough gap to appear in the result.

thusi
Starting Member

25 Posts

Posted - 2008-09-20 : 20:53:15

ah..yup, it all makes sense now. Perfect solution to my problem! Thanks Arnold :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources