split from date and To date in multiple months using oracle sql

Question

In need to split lines with From date and To date in multiple months.

I want to split like this.Target

Sample 1

• 10/02/2023 - 28/02/2023

Target

• 10/02/2023 - 28/02/2023

Sample 2

• 10/02/2023 - 29/08/2023

Target

• 10/02/2023 - 28/02/2023
• 01/03/2023 - 31/03/2023
• 01/04/2023 - 29/08/2023

Sample 3

• 01/04/2022 - 31/03/2023

Target

• 01/04/2022 - 28/02/2023
• 01/03/2023 - 31/03/2023

I succeed in first steps but I'm now stucked For the moment I can only do like this[Existing] But in yellow wrong values,

Here below my code

 CASE WHEN qd.valid_from >= TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM') 
     THEN
     TRUNC(qd.valid_from)
     ELSE
     TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')
     END new_start_date,

     CASE WHEN last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')) >= last_day(TRUNC(add_months(qd.valid_from,2),'MM'))
     THEN
     TRUNC(qd.valid_to)
     ELSE
       
     TRUNC(last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')))
     
     END new_end_date

   FROM QUOTATIONS_UO QH
   ),
   TABLE(
   CAST(
   MULTISET
   (
   SELECT LEVEL
   FROM dual
   CONNECT BY add_months(TRUNC(qd.valid_from,'MM'),LEVEL - 1) <= add_months(TRUNC(qd.valid_from,'MM'),2)
   ) AS sys.OdciNumberList
  )
 )
)

Answer 1

It is unclear what is it about months february and march vs all the others, but if you want to split dates by months then maybe this could help you:

Select DISTINCT
    ID,
    LEVEL "LVL",
    CASE WHEN LEVEL = 1 THEN FROM_DATE END "FROM_DATE",
    CASE WHEN LEVEL = 1 THEN UNTIL_DATE END "UNTIL_DATE",
    CASE  WHEN MONTH_UNTIL = MONTH_FROM THEN FROM_DATE
          WHEN LEVEL = 1 THEN FROM_DATE
          WHEN MONTHS > 1 And LEVEL < MONTHS THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 2)) + 1
          WHEN MONTHS > 1 And LEVEL = MONTHS THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 2)) + 1
          WHEN MONTHS > 0 And MONTHS < LEVEL THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 2)) + 1
    ELSE  UNTIL_DATE
    END "FROM_DATE_2",
    --
    CASE  WHEN MONTH_UNTIL = MONTH_FROM THEN UNTIL_DATE
          --WHEN LEVEL = 1 THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 1))
          WHEN MONTHS > 1 And LEVEL < MONTHS THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 1))
          WHEN MONTHS > 1 And LEVEL = MONTHS THEN LAST_DAY(ADD_MONTHS(FROM_DATE, LEVEL - 1))
    ELSE  UNTIL_DATE
    END "UNTIL_DATE_2"
FROM
    (
        Select 
            ID, FROM_DATE, UNTIL_DATE,
            To_Char(FROM_DATE, 'yyyymm') "MONTH_FROM",
            To_Char(UNTIL_DATE, 'yyyymm') "MONTH_UNTIL",
            FLOOR(MONTHS_BETWEEN(UNTIL_DATE, FROM_DATE)) "MONTHS"
        From    
            tbl
    )
CONNECT BY LEVEL < MONTHS + 2
ORDER BY ID, LVL

... which with sample data like here:

WITH
    tbl (ID, FROM_DATE, UNTIL_DATE) AS
        (
            Select  1, To_Date('10.02.2023', 'dd.mm.yyyy'), To_Date('28.02.2023', 'dd.mm.yyyy') From Dual Union All
            Select  2, To_Date('10.02.2023', 'dd.mm.yyyy'), To_Date('29.08.2023', 'dd.mm.yyyy') From Dual Union All
            Select  3, To_Date('01.04.2022', 'dd.mm.yyyy'), To_Date('31.03.2023', 'dd.mm.yyyy') From Dual 
        )

... results as:

        ID        LVL FROM_DATE UNTIL_DATE FROM_DATE_2 UNTIL_DATE_2
---------- ---------- --------- ---------- ----------- ------------
         1          1 10-FEB-23 28-FEB-23  10-FEB-23   28-FEB-23    
         2          1 10-FEB-23 29-AUG-23  10-FEB-23   28-FEB-23    
         2          2                      01-MAR-23   31-MAR-23    
         2          3                      01-APR-23   30-APR-23    
         2          4                      01-MAY-23   31-MAY-23    
         2          5                      01-JUN-23   30-JUN-23    
         2          6                      01-JUL-23   31-JUL-23    
         2          7                      01-AUG-23   29-AUG-23    
         3          1 01-APR-22 31-MAR-23  01-APR-22   30-APR-22    
         3          2                      01-MAY-22   31-MAY-22    
         3          3                      01-JUN-22   30-JUN-22    
         3          4                      01-JUL-22   31-JUL-22    
         3          5                      01-AUG-22   31-AUG-22    
         3          6                      01-SEP-22   30-SEP-22    
         3          7                      01-OCT-22   31-OCT-22    
         3          8                      01-NOV-22   30-NOV-22    
         3          9                      01-DEC-22   31-DEC-22    
         3         10                      01-JAN-23   31-JAN-23    
         3         11                      01-FEB-23   28-FEB-23    
         3         12                      01-MAR-23   31-MAR-23 

Answer 2

Thanks a lot for this very useful sample which helped me in better understanding. What is complicated for me is that I want specific beahavior depending on Date From if in the past, first line should end at current month. I succeeded in having what I want as first lines but I can't delete the other ones. See below the result in yellow lines I want to keep , others should not appear but seems difficult to avoid.

Answer 3

CASE WHEN qd.valid_from >= TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM') THEN TRUNC(qd.valid_from) ELSE CASE WHEN COLUMN_VALUE = 2 and qd.valid_from < TRUNC(add_months(sysdate,0),'MM') THEN TRUNC(add_months(sysdate, 1),'MM') ELSE
TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM') END
END new_start_date,

     CASE WHEN (COLUMN_VALUE = 1 and qd.valid_from <  TRUNC(add_months(sysdate,0),'MM'))
            THEN TRUNC(Last_Day(TRUNC(sysdate)))
          WHEN (COLUMN_VALUE = 2 and qd.valid_from <  TRUNC(add_months(sysdate,0),'MM'))     
            THEN TRUNC(Last_Day(TRUNC(add_months(sysdate, 1),'MM')))
          ELSE     
              (CASE WHEN ((last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM')) > qd.valid_to) OR COLUMN_VALUE =3)
              THEN qd.valid_to                                  
                ELSE
                  last_day(TRUNC(add_months(qd.valid_from,COLUMN_VALUE - 1),'MM'))
              END)   
     END new_end_date

[In yellow fields to keep - others should not][1]

    [1]: https://i.stack.imgur.com/N46R2.png