I have a form which is connected to database, so this form can have more blocks, where each block have date from, date until for example

I'm trying to make a SELECT statement which going to display number of days between 01.08.2022 and 31.08.2022.

• first block date_from = 25.07.2022, date_until = 11.08.2022 ->11 days
• second block and third block should remain NULL or some default text, because the interval of these blocks is in fourth block.
• fourth block date_from = 11.08.2022, date_until = 05.09.2022-> 20 days (until the end of the month).

Could you help me guys with creating this select? The select should have date_from, date_until and number of days.

CodePudding user response：

WITH data(Block,Date_From,Date_until) AS (
    SELECT 1, TO_DATE('25.07.2022','DD.MM.YYYY'), TO_DATE('11.08.2022','DD.MM.YYYY') FROM DUAL UNION ALL
    SELECT 2, TO_DATE('05.08.2022','DD.MM.YYYY'), TO_DATE('15.08.2022','DD.MM.YYYY') FROM DUAL UNION ALL
    SELECT 3, TO_DATE('10.08.2022','DD.MM.YYYY'), TO_DATE('20.08.2022','DD.MM.YYYY') FROM DUAL UNION ALL
    SELECT 4, TO_DATE('11.08.2022','DD.MM.YYYY'), TO_DATE('05.09.2022','DD.MM.YYYY') FROM DUAL -- UNION ALL
),
clipped(Block,Date_From,Date_until) AS (
    SELECT Block, GREATEST(Date_From, TO_DATE('01.08.2022','DD.MM.YYYY')), LEAST(Date_until, TO_DATE('31.08.2022','DD.MM.YYYY')) FROM DATA
)
SELECT c.*, 
    CASE WHEN NOT(
        EXISTS(SELECT 1 FROM clipped d WHERE d.Date_From < c.Date_until AND d.Date_until > c.Date_From AND d.block < c.block) 
        AND
        EXISTS(SELECT 1 FROM clipped d WHERE d.Date_From < c.Date_until AND d.Date_until > c.Date_From AND d.block > c.block) )
        THEN c.Date_until - c.Date_from  ELSE NULL
    END AS days
FROM clipped c
ORDER BY c.block 
;


1   01/08/22    11/08/22    10
2   05/08/22    15/08/22    
3   10/08/22    20/08/22    
4   11/08/22    31/08/22    20