Calculating days of therapy in a sql query

Question

I'm attempting to calculate days of therapy by month from an oracle database. The (vastly simplified) data is as follows:

 Therapies                          
+-----------+-----------+----------+
| Rx Number | StartDate | StopDate |
|-----------+-----------+----------|
| 1         | 12-29-14  | 1-10-15  |
| 2         | 1-2-15    | 1-14-15  |
| 3         | 1-29-15   | 2-15-15  |
+-----------+-----------+----------+

For the purposes of this example, all times are assumed to be midnight. The total days of therapy in this table is (10-1 + 32-29) + (14-2) + (15-1 + 32-29) = 41. The total days of therapy in January in this table is (10-1) + (14-2) + (32-29) = 24.

If I wanted to calculate days of therapy for the month of January , my best effort is the following query:

SELECT SUM(stopdate - startdate) 
FROM therapies 
WHERE startdate > to_date('01-JAN-15') 
  AND stopdate < to_date ('01-FEB-15');

However, rx's 1 and 3 are not captured at all. I could try the following instead:

SELECT SUM(stopdate - startdate) 
FROM therapies 
WHERE stopdate > to_date('01-JAN-15') 
  AND startdate < to_date ('01-FEB-15');

But that would include the full duration of the first and third therapies, not just the portion in January. To make the matter more complex, I need these monthly summaries over a period of two years. So my questions are:

1. How do I include overhanging therapies such that only the portion within the target time period is included, and
2. How do I automatically generate these monthly summaries over a two year period?

Answer 1

How do I include overhanging therapies such that only the portion within the target time period is included?

select sum(
    greatest(least(stopdate, date '2015-01-31' + 1) 
      - greatest(startdate, date '2015-01-01'), 0)) suma
  from therapies

How do I automatically generate these monthly summaries over a two year period?

with period as (select date '2014-01-01' d1, date '2015-12-31' d2 from dual),
months as (select trunc(add_months(d1, level-1), 'Month') dt 
    from period connect by add_months(d1, level-1)<d2)
select to_char(dt, 'yyyy-mm') mth, 
    sum(greatest(least(stopdate, add_months(dt, 1)) - greatest(startdate, dt), 0)) suma
  from therapies, months
  group by to_char(dt, 'yyyy-mm') order by mth

Above queries produced desired output. Please insert your dates in proper places to change analyzed periods. In second SQL inner subquery months gives 24 dates, one for each month. The rest is only maneuvering with functions greatest(),least() and some math.

Answer 2

This is a bit tricky, as you need to capture days from sessions that:

• Begin before the month and end after the month
• Begin before the month and end during the month
• Begin during the month and end after the month
• Begin during the month and end during the month

To get those sessions, you can use a WHERE statement like this (the @ symbol means that those are variables being passed in):

*examples are in TSQL, PLSQL might have somewhat different syntax

WHERE startdate < @endDate AND stopdate > @startDate

That should capture all four of those scenarios that I listed.

Then you only need to capture days that occurred during the month. I do this with a query that replaces the startdate/enddate with the date range limits if they exceed the range, like this:

SELECT
  CASE WHEN enddate > @endDate then @endDate ELSE enddate END -
  CASE WHEN startdate < @startDate THEN @startDate ELSE startdate END

So your whole query should look like this:

SELECT
  SUM(
    CASE WHEN enddate > @endDate then @endDate ELSE enddate END -
    CASE WHEN startdate < @startDate THEN @startDate ELSE startdate END
  )
FROM therapies 
WHERE startdate < @endDate AND stopdate > @startDate

If you want to run that for two years, toss that code in a function that accepts @startDate and @endDate parameters, then call it from a query that gives you two years worth of months, like this:

WITH dateCTE AS (
  SELECT 
    GETDATE() AS StartDate,
    DATEADD(Month, 1, GETDATE()) AS EndDate

    UNION ALL

    SELECT 
        DATEADD(MONTH, -1, StartDate),
        DATEADD(MONTH, -1, EndDate)
    FROM dateCTE
    WHERE StartDate > DATEADD(YEAR, -2, GETDATE())
)

SELECT 
    StartDate, 
    EndDate,
    SomeFunction(StartDate, EndDate)
FROM dateCTE

Answer 3

I would do something like the following:

WITH t1 AS (
  SELECT 1 AS rx, DATE'2014-12-29' AS start_date
       , DATE'2015-01-10' AS stop_date
    FROM dual
   UNION ALL
  SELECT 2, DATE'2015-01-02', DATE'2015-01-14'
    FROM dual
   UNION ALL
  SELECT 3, DATE'2015-01-29', DATE'2015-02-15'
    FROM dual
)
SELECT TRUNC(rx_dt, 'MONTH') AS rx_month, SUM(rx_cnt) AS rx_day_cnt
  FROM (
    SELECT rx_dt, COUNT(*) AS rx_cnt
      FROM (
        SELECT rx, start_date + LEVEL - 1 AS rx_dt
          FROM t1
       CONNECT BY start_date + LEVEL - 1 < stop_date
           AND PRIOR rx = rx
           AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
    ) GROUP BY rx_dt
) GROUP BY TRUNC(rx_dt, 'MONTH')
 ORDER BY rx_month

Results:

12/1/2014 12:00:00 AM   2
1/1/2015 12:00:00 AM    24
2/1/2015 12:00:00 AM    15

See SQL Fiddle here.

What I am doing is using LEVEL and CONNECT BY to get all the days of therapy based on start_date and stop_date (not inclusive). I then GROUP BY the therapy date (rx_dt) to handle the overlapping therapies. Then I GROUP BY the month of the therapy using the TRUNC() function.

This should work just fine over a two-year period (or more); just add that filter before the last GROUP BY:

WHERE rx_dt >= DATE'2014-01-01'
  AND rx_dt < DATE'2016-01-01'
GROUP BY TRUNC(rx_dt, 'MONTH')

Note that if your primary key is composite, you should include all the columns in the CONNECT BY clause:

CONNECT BY start_date + LEVEL - 1 < stop_date
    AND PRIOR rx = rx
    AND PRIOR patient_id = patient_id
    --etc.

Answer 4

Use a case statement to set the start date and stop date. Like the below:

select sum( Stopdate - (case Startdate when startdate < to_date(@YourBeginingDate) then To_date(@YourBeginingDate) else startdate end) FROM therapies WHERE stopdate > to_date(@YourBeginingDate) AND StartDate < to_date(@YourEndingDate)