How to generate a list periods of each every 30 days from start to today

Question

I works for a company which request me generate a list of period for each 30 days since the item start until now. Sample: Item 'A' has begin date is 01/DEC/2016, and Item 'B' has begin date is 05/Feb/2016. Today is 07/FEB/2017.

The output should like this:

**ITEM     | START      | END       | PERIOD_NO**
----------------------------------------------
A          |01/12/2016  |30/12/2016 |0
A          |31/12/2016  |29/01/2017 |1
A          |30/01/2017  |28/02/2017 |2
B          |05/07/2016  |03/08/2016 |0
B          |04/08/2016  |02/09/2016 |1
B          |03/09/2016  |02/10/2016 |2
B          |03/10/2016  |01/11/2016 |3
B          |02/11/2016  |01/12/2016 |4
B          |02/12/2016  |31/12/2016 |5
B          |01/01/2017  |30/01/2017 |6
B          |31/01/2017  |01/03/2017 |7

Here is my code:

select 
          ITEM
          , trunc(Start_Date+(level*30)-30) AS BEGIN
          , CASE WHEN  (Level-1) = 0 THEN  trunc(Start_Date+(level*30) - 1) ELSE trunc(Start_Date+(level*30) -1) END AS END
          ,  Level-1 AS Period          
    from 
    (
         Select  'A' ITEM
                ,To_Date('05/07/2016', 'dd/MM/YYYY') Start_Date 
                , TRUNC(sysdate)  END_Date                            
                 From Dual
      /*  UNION ALL
         Select
               'B' ITEM
               , To_Date('01/02/2014', 'dd/MM/YYYY') Start_Date 
                , TRUNC(sysdate)  END_Date                            
                 From Dual*/
    ) t
    connect by level < ( ( (END_DATE - START_DATE) / 30) + 1)

As it only works for just one item, I would like to seek your advice how to correct it to works for 2 or more items.

Thank you in advance.

Which version of Oracle are you using? – Gurwinder Singh Feb 07 '17 at 13:40 — Gurwinder Singh, Feb 07 '17 at 13:40

David דודו Markovitz · Accepted Answer · 2017-02-07T15:03:42.923

with    t (item,start_date) as 
        (
                        select 'A',date '2016-12-01' from dual
            union all   select 'B',date '2016-02-05' from dual
        )

select          t.item
               ,start_date + 30*(level-1)       as start_date
               ,start_date + 30*level - 1       as end_date
               ,level - 1                       as period_no

from            t        

connect by      item = prior item
            and level <= 1 + (sysdate - start_date) / 30
            and prior sys_guid () is not null

+------+---------------------+---------------------+-----------+
| ITEM | START_DATE          | END_DATE            | PERIOD_NO |
+------+---------------------+---------------------+-----------+
| A    | 2016-12-01 00:00:00 | 2016-12-30 00:00:00 | 0         |
+------+---------------------+---------------------+-----------+
| A    | 2016-12-31 00:00:00 | 2017-01-29 00:00:00 | 1         |
+------+---------------------+---------------------+-----------+
| A    | 2017-01-30 00:00:00 | 2017-02-28 00:00:00 | 2         |
+------+---------------------+---------------------+-----------+
| B    | 2016-02-05 00:00:00 | 2016-03-05 00:00:00 | 0         |
+------+---------------------+---------------------+-----------+
| B    | 2016-03-06 00:00:00 | 2016-04-04 00:00:00 | 1         |
+------+---------------------+---------------------+-----------+
| B    | 2016-04-05 00:00:00 | 2016-05-04 00:00:00 | 2         |
+------+---------------------+---------------------+-----------+
| B    | 2016-05-05 00:00:00 | 2016-06-03 00:00:00 | 3         |
+------+---------------------+---------------------+-----------+
| B    | 2016-06-04 00:00:00 | 2016-07-03 00:00:00 | 4         |
+------+---------------------+---------------------+-----------+
| B    | 2016-07-04 00:00:00 | 2016-08-02 00:00:00 | 5         |
+------+---------------------+---------------------+-----------+
| B    | 2016-08-03 00:00:00 | 2016-09-01 00:00:00 | 6         |
+------+---------------------+---------------------+-----------+
| B    | 2016-09-02 00:00:00 | 2016-10-01 00:00:00 | 7         |
+------+---------------------+---------------------+-----------+
| B    | 2016-10-02 00:00:00 | 2016-10-31 00:00:00 | 8         |
+------+---------------------+---------------------+-----------+
| B    | 2016-11-01 00:00:00 | 2016-11-30 00:00:00 | 9         |
+------+---------------------+---------------------+-----------+
| B    | 2016-12-01 00:00:00 | 2016-12-30 00:00:00 | 10        |
+------+---------------------+---------------------+-----------+
| B    | 2016-12-31 00:00:00 | 2017-01-29 00:00:00 | 11        |
+------+---------------------+---------------------+-----------+
| B    | 2017-01-30 00:00:00 | 2017-02-28 00:00:00 | 12        |
+------+---------------------+---------------------+-----------+

Two minor points about performance: `prior sys_guid() is not null` has the same effect and is slightly faster than comparing to "current" `sys_guid()`; and the cutoff condition is best written as `level <= 1 + (sysdate - start_date) / 30` so that the RHS is computed just once; otherwise the arithmetic is different for every row, so it will be computed for every row. — , Feb 07 '17 at 14:33

Gurwinder Singh · Answer 2 · 2017-02-07T14:11:20.423

In Oracle 11g and below, you can use nested table to achieve this:

with t (dt) as (
    select to_date('01/12/2016','dd/mm/yyyy') from dual union all
    select to_date('05/07/2016','dd/mm/yyyy') from dual
)
select
    t.dt + (x.column_value - 1) * 30 start_date,
    t.dt +  x.column_value * 30 - 1  end_date,
    x.column_value - 1 period_id
from t cross join table(cast(
    multiset(
        select level
        from dual
        connect by t.dt + 30 * (level - 1) <= sysdate
    ) as sys.odcinumberlist
)) x;

If you are using Oracle 12c+, you can use OUTER APPLY which greatly simplifies the syntax:

with t (dt) as (
    select to_date('01/12/2016','dd/mm/yyyy') from dual union all
    select to_date('05/07/2016','dd/mm/yyyy') from dual
)
select
    t.dt + (x.n - 1) * 30 start_date,
    t.dt +  x.n * 30 - 1  end_date,
    x.n - 1 period_id
from t outer apply (
    select level n
    from dual
    connect by t.dt + 30 * (level - 1) <= sysdate
) x;

Both the above query produces:

+------------+-----------+-----------+
| START_DATE | END_DATE  | PERIOD_ID |
+------------+-----------+-----------+
| 01-DEC-16  | 30-DEC-16 | 0         |
+------------+-----------+-----------+
| 31-DEC-16  | 29-JAN-17 | 1         |
+------------+-----------+-----------+
| 30-JAN-17  | 28-FEB-17 | 2         |
+------------+-----------+-----------+
| 05-JUL-16  | 03-AUG-16 | 0         |
+------------+-----------+-----------+
| 04-AUG-16  | 02-SEP-16 | 1         |
+------------+-----------+-----------+
| 03-SEP-16  | 02-OCT-16 | 2         |
+------------+-----------+-----------+
| 03-OCT-16  | 01-NOV-16 | 3         |
+------------+-----------+-----------+
| 02-NOV-16  | 01-DEC-16 | 4         |
+------------+-----------+-----------+
| 02-DEC-16  | 31-DEC-16 | 5         |
+------------+-----------+-----------+
| 01-JAN-17  | 30-JAN-17 | 6         |
+------------+-----------+-----------+
| 31-JAN-17  | 01-MAR-17 | 7         |
+------------+-----------+-----------+

The below query produces rows for each row of your table using Hierarchical CONNECT BY:

    select level n
    from dual
    connect by t.dt + 30 * (level - 1) <= sysdate

And the CTE below is to build a test table:

with t (dt) as (
    select to_date('01/12/2016','dd/mm/yyyy') from dual union all
    select to_date('05/07/2016','dd/mm/yyyy') from dual
)

score 1 · Answer 3 · answered Feb 07 '17 at 13:43

Numbers CTE, a bit of maths and a case statement

With numbers (NN) as
(
 select 0 as NN
 from dual
 union all 
 select NN+1
 from numbers
 where NN < 100
)
, CTE as
(
 select item, 
        StartDate+(NN*30) as StartList, 
        row_number() over(partition by ITEM order by StartDate+(NN*30)) as PeriodNo
 from Numbers
 cross join MyTable
 where StartDate+(NN*30) < sysdate
)
select Item, 
       StartList, 
       case 
         when StartList +29 > sysdate then sysdate 
         else StartList +29 > sysdate 
       end as enddate, 
       PeriodNo
from CTE

How to generate a list periods of each every 30 days from start to today

3 Answers3