CODEWITHSUNDEEP
sqloracle-database
Nawaf
Nawaf

Reputation: 540

Calculate difference date time for each record per month

Let's suppose I have the following table. How can I can separately calculate the delay for each month. 

Req start_dt    end_dt
1   1/2/2017    3/5/2017
2   5/2/2017    7/6/2017

I want the result to be like the following table.

Req start_dt    end_dt     delay    MM
1   1/2/2017    3/5/2017    30      1
1   1/2/2017    3/5/2017    28      2
1   1/2/2017    3/5/2017    5       3
2   5/2/2017    7/6/2017    30      5
2   5/2/2017    7/6/2017    30      6
2   5/2/2017    7/6/2017    6       7

Upvotes: 3

Views: 95

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You need to split your date ranges into multiple months, with the full range for intermediate months and partial ranges for the start and end months. One way to do that is with recursive subquery factoring

with rcte (req, start_dt, end_dt, period_start_dt, period_end_dt) as (
  select req, start_dt, end_dt, start_dt,
    case when trunc(end_dt, 'MM') = trunc(start_dt, 'MM') then end_dt
      else last_day(start_dt) end
  from your_table
  union all
  select req, start_dt, end_dt, add_months(trunc(period_start_dt, 'MM'), 1),
    case when trunc(end_dt, 'MM') = add_months(trunc(period_start_dt, 'MM'), 1) then end_dt
      else last_day(add_months(trunc(period_start_dt, 'MM'), 1) ) end
  from rcte
  where trunc(end_dt, 'MM') > trunc(period_start_dt, 'MM')
)
select req, start_dt, end_dt, period_start_dt, period_end_dt,
  period_end_dt - period_start_dt + 1 as delay,
  extract(month from period_start_dt) as mm
from rcte
order by req, period_start_dt, period_end_dt;

       REQ START_DT   END_DT     PERIOD_STA PERIOD_END      DELAY         MM
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 2017-01-02 2017-03-05 2017-01-02 2017-01-31         30          1
         1 2017-01-02 2017-03-05 2017-02-01 2017-02-28         28          2
         1 2017-01-02 2017-03-05 2017-03-01 2017-03-05          5          3
         2 2017-05-02 2017-07-06 2017-05-02 2017-05-31         30          5
         2 2017-05-02 2017-07-06 2017-06-01 2017-06-30         30          6
         2 2017-05-02 2017-07-06 2017-07-01 2017-07-06          6          7

The recursive CTE has an anchor member which gets the initial data from your table, and calculates the start and end of the first period. The period start is the original range start date; the period end is either the range end date (if it's in the same month), or the end of that month.

The recursive member then uses the values from the anchor and generates a new period, which will start at the beginning of the following month, and again end either at the original range end date or the end of that month.

Once you have those period start/end dates it's simple to calculate the difference using normal date subtraction. (I've left the period start/end dates in the output to try and make it a bit clearer; just remove from the final select list if you don't want them.)

Slightly simpler calculation for the period start/end dates:

with rcte (req, start_dt, end_dt, period_start_dt, period_end_dt) as (
  select req, start_dt, end_dt, start_dt, least(end_dt, last_day(start_dt))
  from your_table
  union all
  select req, start_dt, end_dt, add_months(trunc(period_start_dt, 'MM'), 1),
    least(end_dt, last_day(add_months(trunc(period_start_dt, 'MM'), 1)))
  from rcte
  where trunc(end_dt, 'MM') > trunc(period_start_dt, 'MM')
)
select req, start_dt, end_dt,
  period_end_dt - period_start_dt + 1 as delay,
  extract(month from period_start_dt) as mm
from rcte
order by req, period_start_dt, period_end_dt;

       REQ START_DT   END_DT          DELAY         MM
---------- ---------- ---------- ---------- ----------
         1 2017-01-02 2017-03-05         30          1
         1 2017-01-02 2017-03-05         28          2
         1 2017-01-02 2017-03-05          5          3
         2 2017-05-02 2017-07-06         30          5
         2 2017-05-02 2017-07-06         30          6
         2 2017-05-02 2017-07-06          6          7

Upvotes: 2

MT0
MT0

Reputation: 167972

You can do it with correlated hierarchical queries:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Req, start_dt, end_dt ) AS
SELECT 1, DATE '2017-01-02', DATE '2017-03-05' FROM DUAL UNION ALL
SELECT 2, DATE '2017-05-02', DATE '2017-07-06' FROM DUAL;

Query 1:

SELECT t.*,
       LEAST( LAST_DAY( d.COLUMN_VALUE ), t.end_dt )
         - GREATEST( d.COLUMN_VALUE, t.start_dt ) + 1 AS delay,
       EXTRACT( MONTH FROM d.COLUMN_VALUE ) AS MM
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT ADD_MONTHS( TRUNC( t.start_dt, 'MM' ), LEVEL - 1 )
             FROM   DUAL
             CONNECT BY LEVEL <= MONTHS_BETWEEN( t.end_dt, TRUNC( t.start_dt, 'MM' ) ) + 1
           ) AS SYS.ODCIDATELIST
         )
       ) d

Results:

| REQ |             START_DT |               END_DT | DELAY | MM |
|-----|----------------------|----------------------|-------|----|
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |    30 |  1 |
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |    28 |  2 |
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |     5 |  3 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |    30 |  5 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |    30 |  6 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |     6 |  7 |

Upvotes: 2

Related Questions