All geek questions in one place

How to create list periods every 30 days from the beginning to today

I work for a company that asks me to generate a list of periods for every 30 days from the launch of this item to the present. Example: Item “A” has a start date of 01 / DEC / 2016, and the start date of “B” is 05 / Feb / 2016. Today is 07 / FEB / 2017.

The output should look 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)

Since it only works for one element, I would like your advice on how to fix it to work for 2 or more elements.

Thanks in advance.

+4
source share
3 answers
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        |
+------+---------------------+---------------------+-----------+
+4
source

In Oracle 11g and below, you can use a nested table to do 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;

Oracle 12c +, OUTER APPLY, :

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;

:

+------------+-----------+-----------+
| 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         |
+------------+-----------+-----------+

, CONNECT BY:

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

CTE - :

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
)
+3

CTE numbers, math bit, and 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
+1
source

Source: https://habr.com/ru/post/1669038/

More articles:


All Articles