Create months column for every client id in data range

user_16437763 0
Viewed 60

I have data, looking something like this:

client_id   open_date   close_date
1          2020-01-01   2020-06-06

with different client and different dates.

and i need to change it to something like this:

date           client_id   open_date   close_date
2020-01-01      1          2020-01-01   2020-06-06
2020-02-01      ​1          2020-01-01   2020-06-06
2020-03-01      ​1          2020-01-01   2020-06-06
2020-04-01      ​1          2020-01-01   2020-06-06
2020-05-01      ​1          2020-01-01   2020-06-06
2020-06-01      ​1          2020-01-01   2020-06-06
2020-07-01      ​1          2020-01-01   2020-06-06
2020-08-01      ​1          2020-01-01   2020-06-06
2020-09-01      ​1          2020-01-01   2020-06-06

Where client's data connects to every month in a sequence that i define + 3(or more\less) months from close_date. Ideally in oracle code...

oracle sql
4 Answers

Yet another option, dealing with more than a single client.

SQL> with test (client_id, open_date, close_date) as
  2    (select 1, date '2020-01-01', date '2020-06-06' from dual union all
  3     select 2, date '2021-04-15', date '2021-06-23' from dual
  4    )
  5  select add_months(trunc(open_date, 'mm'), column_value - 1) datum,
  6         client_id,
  7         open_date,
  8         close_date
  9  from test cross join
 10       table(cast(multiset(select level from dual
 11                           connect by level <=
 12                             trunc(months_between(add_months(close_date, 3), open_date)) + 1
 13                          ) as sys.odcinumberlist))
 14  order by client_id, datum;

DATUM       CLIENT_ID OPEN_DATE  CLOSE_DATE
---------- ---------- ---------- ----------
2020-01-01          1 2020-01-01 2020-06-06
2020-02-01          1 2020-01-01 2020-06-06
2020-03-01          1 2020-01-01 2020-06-06
2020-04-01          1 2020-01-01 2020-06-06
2020-05-01          1 2020-01-01 2020-06-06
2020-06-01          1 2020-01-01 2020-06-06
2020-07-01          1 2020-01-01 2020-06-06
2020-08-01          1 2020-01-01 2020-06-06
2020-09-01          1 2020-01-01 2020-06-06
2021-04-01          2 2021-04-15 2021-06-23
2021-05-01          2 2021-04-15 2021-06-23
2021-06-01          2 2021-04-15 2021-06-23
2021-07-01          2 2021-04-15 2021-06-23
2021-08-01          2 2021-04-15 2021-06-23
2021-09-01          2 2021-04-15 2021-06-23

15 rows selected.

SQL>
user_90979060

Using two FOR LOOP you can do the above. CLIENT_DATA is the first table in which client details are there, and NEW_CLIENT_DATA you can enter the details with new datails.

CREATE PROCEDURE SP_CLIENT_DATES
AS
P_NEW_DATE DATE;
P_OPEN_DATE_MONTH NUMBER;
P_CLOSE_DATE_MONTH NUMBER;
BEGIN

    -- Loop to get all the client lists
    FOR CLIENTS IN ( SELECT CLIENT_ID, OPEN_DATE, CLOSE_DATE FROM CLIENT_DATA)
    LOOP 
    
    P_NEW_DATE := CLIENTS.OPEN_DATE;
    P_OPEN_DATE_MONTH  :=  EXTRACT ( MONTH FROM CLIENTS.OPEN_DATE);
    P_CLOSE_DATE_MONTH :=  EXTRACT ( MONTH FROM CLIENTS.CLOSE_DATE) + 3;
    
    -- Loop to insert client ID with current opening month to three months more to closing month
        FOR I  IN P_OPEN_DATE_MONTH .. P_CLOSE_DATE_MONTH
    
            LOOP
                INSERT INTO NEW_CLIENT_DATA
                (NEW_DATE, CLIENT_ID, OPEN_DATE, CLOSE_DATE)
                SELECT P_NEW_DATE, CLIENTS.CLIENT_ID, CLIENTS.OPEN_DATE, CLIENTS.CLOSE_DATE FROM DUAL;
                
                P_NEW_DATE := ADD_MONTHS(P_NEW_DATE, 1);
    
            END LOOP;
    END LOOP;
    
 COMMIT;
END;

Hope the above will work for you.

user_41237760

Here is another solution. Replace the "3" with any value to adjust the months before and after close date.

WITH 
clients_table (id, open_date, close_date )
AS
(
  SELECT 1, TO_DATE('2020-01-01','YYYY-MM-DD'),TO_DATE('2020-06-01','YYYY-MM-DD') FROM DUAL
),
month_range (val) AS 
(
  SELECT 3 FROM DUAL
)
SELECT id, TO_CHAR(close_date_per_month,'YYYY-MM-DD')
  FROM clients_table c
       CROSS JOIN LATERAL
       (
         SELECT ADD_MONTHS(TRUNC(close_date,'MM'), LEVEL - (val + 1) ) close_date_per_month 
           FROM clients_table, month_range CONNECT BY level < (val + 1) * 2
           
       );
user_41898140

You can try below query -

WITH DATA AS (SELECT 1 AS client_id, DATE '2020-01-01' AS open_date, DATE '2020-06-06' AS close_date
                FROM DUAL)
SELECT ADD_MONTHS(OPEN_DATE, LEVEL - 1) NEW_DATE, client_id, open_date, close_date
  FROM DATA
CONNECT BY LEVEL <= EXTRACT(MONTH FROM close_date) + 3

Demo.

user_36277560
Related