Count of days in a period

Question

I have list of items that have start and end date. Items belong to user. For one item the period can range from 1-5 years. I want to find the count of days that are between the given date range which I would pass from query. Period start is always sysdate and end sysdate - 5 years

The count of days returned must also be in the period range.

Example:

I initiate a query as of 15.05.2015) as me being user, so I need to find all days between 15.05.2010 and 15.05.2015

During that period 2 items have belong to me:

Item 1) 01.01.2010 - 31.12.2010. Valid range: 15.05.2010 - 31.12.2010 = ~195 days

Item 2) 01.01.2015 - 31.12.2015. Valid range: 01.01.2015 - 15.05.2015 = ~170 days

I need a sum of these days that are exactly in that period.

For query right now I just have the count which takes the full range of an item (making it simple):

SELECT SUM(i.end_date - i.start_date) AS total_days 
FROM items i 
WHERE i.start_date >= to_date('2010-15-05', 'yyyy-mm-dd')
AND i.end_date <= to_date('2015-15-05', 'yyyy-mm-dd')
AND i.user = 'me'

So right now this would give me about count of 2 year period dates which is wrong, how should I update my select sum to include the dates that are in the period? Correct result would be 195 + 170. Currently I would get like 365 + 365 or something.

Answer 1

Period start is always sysdate and end sysdate - 5 years

You can get this using: SYSDATE and SYSDATE - INTERVAL '5' YEAR

Item 1) 01.01.2010 - 31.12.2010. Valid range: 15.05.2010 - 31.12.2010 = ~195 days

Item 2) 01.01.2015 - 31.12.2015. Valid range: 01.01.2015 - 15.05.2015 = ~170 days

Assuming these examples show start_date - end_date and the valid range is your expected answer for that particular SYSDATE then you can use:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE items ( "user", start_date, end_date ) AS
          SELECT 'me', DATE '2010-01-01', DATE '2010-12-31' FROM DUAL
UNION ALL SELECT 'me', DATE '2015-01-01', DATE '2015-12-31' FROM DUAL
UNION ALL SELECT 'me', DATE '2009-01-01', DATE '2009-12-31' FROM DUAL
UNION ALL SELECT 'me', DATE '2009-01-01', DATE '2016-12-31' FROM DUAL
UNION ALL SELECT 'me', DATE '2012-01-01', DATE '2012-12-31' FROM DUAL
UNION ALL SELECT 'me', DATE '2013-01-01', DATE '2013-01-01' FROM DUAL;

Query 1:

SELECT "user",
       TO_CHAR( start_date, 'YYYY-MM-DD' ) AS start_date,
       TO_CHAR( end_date, 'YYYY-MM-DD' ) AS end_date,
       TO_CHAR( GREATEST(TRUNC(i.start_date), TRUNC(SYSDATE)-INTERVAL '5' YEAR), 'YYYY-MM-DD' ) AS valid_start,
       TO_CHAR( LEAST(TRUNC(i.end_date),TRUNC(SYSDATE)), 'YYYY-MM-DD' ) AS valid_end,
       LEAST(TRUNC(i.end_date),TRUNC(SYSDATE))
         - GREATEST(TRUNC(i.start_date), TRUNC(SYSDATE)-INTERVAL '5' YEAR)
         + 1
         AS total_days 
FROM   items i
WHERE  i."user" = 'me'
AND    TRUNC(i.start_date) <= TRUNC(SYSDATE)
AND    TRUNC(i.end_date)   >= TRUNC(SYSDATE) - INTERVAL '5' YEAR

Results:

| user | START_DATE |   END_DATE | VALID_START |  VALID_END | TOTAL_DAYS |
|------|------------|------------|-------------|------------|------------|
|   me | 2010-01-01 | 2010-12-31 |  2010-05-21 | 2010-12-31 |        225 |
|   me | 2015-01-01 | 2015-12-31 |  2015-01-01 | 2015-05-21 |        141 |
|   me | 2009-01-01 | 2016-12-31 |  2010-05-21 | 2015-05-21 |       1827 |
|   me | 2012-01-01 | 2012-12-31 |  2012-01-01 | 2012-12-31 |        366 |
|   me | 2013-01-01 | 2013-01-01 |  2013-01-01 | 2013-01-01 |          1 |

This assumes that the start date is at the beginning of the day (00:00) and the end date is at the end of the day (24:00) - so, if the start and end dates are the same then you are expecting the result to be 1 total day (i.e. the period 00:00 - 24:00). If you are, instead, expecting the result to be 0 then remove the +1 from the calculation of the total days value.

Query 2:

If you want the sum of all these valid ranges and are happy to count dates in overlapping ranges multiple times then just wrap it in the SUM aggregate function:

SELECT SUM( LEAST(TRUNC(i.end_date),TRUNC(SYSDATE))
         - GREATEST(TRUNC(i.start_date), TRUNC(SYSDATE)-INTERVAL '5' YEAR)
         + 1 )
         AS total_days 
FROM   items i
WHERE  i."user" = 'me'
AND    TRUNC(i.start_date) <= TRUNC(SYSDATE)
AND    TRUNC(i.end_date)   >= TRUNC(SYSDATE) - INTERVAL '5' YEAR

Results:

| TOTAL_DAYS |
|------------|
|       2560 |

Query 3:

Now if you want to get a count of all the valid days in the range and not count overlap in ranges multiple times then you can do:

WITH ALL_DATES_IN_RANGE AS (
  SELECT TRUNC(SYSDATE) - LEVEL + 1 AS valid_date
  FROM   DUAL
  CONNECT BY LEVEL <= SYSDATE - (SYSDATE - INTERVAL '5' YEAR) + 1
)
SELECT COUNT(1) AS TOTAL_DAYS
FROM   ALL_DATES_IN_RANGE a
WHERE  EXISTS ( SELECT 'X'
                FROM   items i
                WHERE  a.valid_date BETWEEN i.start_date AND i.end_date
                AND    i."user" = 'me' )

Results:

| TOTAL_DAYS |
|------------|
|       1827 |

Answer 2

Use a case statement to evaluate the dates set start and end dates based on the case.

Select SUM(
  (case when i.end_date > to_date('2015-15-05','yyyy-mm-dd') then 
   to_date('2015-15-05','yyyy-mm-dd') else 
   i.end_date end) - 
  (case when i.start_date< to_date('2010-15-05','yyyy-mm-dd') then 
    to_date('2010-15-05','yyyy-mm-dd') else 
    i.end_date end)) as total_days
FROM items i 
WHERE i.start_date >= to_date('2010-15-05', 'yyyy-mm-dd')
AND i.end_date <= to_date('2015-15-05', 'yyyy-mm-dd')
AND i.user = 'me'

Answer 3

Assuming the time periods have no overlaps:

SELECT SUM(LEAST(i.end_date, DATE '2015-05-15') -
           GREATEST(i.start_date, DATE '2010-05-15')
          ) AS total_days 
FROM items i 
WHERE i.start_date >= DATE '2010-05-15' AND
      i.end_date <= DATE '2015-05-15' AND
      i.user = 'me';