SQLize | PHPize | SQLtest

CREATE TABLE tab1( st_dt date, end_dt date); INSERT INTO tab1 (st_dt, end_dt) VALUES (TO_DATE('2022-08-01', 'YYYY-MM-DD'), TO_DATE('2022-08-05', 'YYYY-MM-DD')), (TO_DATE('2022-08-06', 'YYYY-MM-DD'), TO_DATE('2022-08-15', 'YYYY-MM-DD')), (TO_DATE('2022-08-20', 'YYYY-MM-DD'), TO_DATE('2022-08-25', 'YYYY-MM-DD')), (TO_DATE('2022-08-28', 'YYYY-MM-DD'), TO_DATE('2022-08-30', 'YYYY-MM-DD')); Select * FROM tab1; SELECT *, extract(day from next_dt) - extract(day from end_dt) AS days_diff, CASE WHEN extract(day from next_dt) - extract(day from end_dt) > 1 THEN 0 else 1 end FROM (SELECT *, LEAD (st_dt, 1) OVER (ORDER BY st_dt) AS next_dt FROM tab1 ) AS subquery_alias ; with tbl as ( select lead(st_dt, 1) over(order by st_dt asc) as lg ,end_dt + 1 as nend from tab1 ) select tbl.* from tbl ;