By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (Name, Check_In, Check_Out) AS
SELECT 'Joey Zolomon', DATE '2023-08-05', DATE '2023-08-15' FROM DUAL UNION ALL
SELECT 'Hunter Zolomon', DATE '2023-08-15', DATE '2023-08-26' FROM DUAL UNION ALL
SELECT 'Barry Allen', DATE '2023-09-02', DATE '2023-09-05' FROM DUAL;
3 rows affected
WITH split_months (check_in, check_out, max_check_out) AS (
SELECT check_in,
LEAST(check_out + 1, ADD_MONTHS(TRUNC(check_in, 'MM'), 1)),
check_out + 1
FROM table_name
UNION ALL
SELECT check_out,
LEAST(max_check_out, ADD_MONTHS(check_out, 1)),
max_check_out
FROM split_months
WHERE check_out < max_check_out
),
presence (prev_dt, dt, next_dt, change, num_present) AS (
SELECT LAG(dt) OVER (
PARTITION BY TRUNC(dt, 'MM')
ORDER BY dt, change DESC, ROWNUM
),
dt,
LEAD(dt) OVER (
PARTITION BY TRUNC(dt, 'MM')
ORDER BY dt, change DESC, ROWNUM
),
change,
SUM(change) OVER (
ORDER BY dt, change DESC, ROWNUM
)
FROM split_months
UNPIVOT ( dt FOR change IN ( Check_In AS 1, Check_Out AS -1 ) )
)
SELECT TRUNC(dt, 'MM') AS month,
SUM(
CASE
WHEN num_present = 0
THEN COALESCE(next_dt, ADD_MONTHS(TRUNC(dt, 'MM'), 1)) - dt
ELSE dt - TRUNC(dt, 'MM')
END
| MONTH | NUM_FREE_DAYS |
|---|---|
| 2023-08-01 00:00:00 | 9 |
| 2023-09-01 00:00:00 | 26 |
WITH split_months (check_in, check_out, max_check_out) AS (
SELECT check_in,
LEAST(check_out + 1, ADD_MONTHS(TRUNC(check_in, 'MM'), 1)),
check_out + 1
FROM table_name
UNION ALL
SELECT check_out,
LEAST(max_check_out, ADD_MONTHS(check_out, 1)),
max_check_out
FROM split_months
WHERE check_out < max_check_out
),
presence (prev_dt, dt, next_dt, change, num_present) AS (
SELECT LAG(dt) OVER (
PARTITION BY TRUNC(dt, 'MM')
ORDER BY dt, change DESC, ROWNUM
),
dt,
LEAD(dt) OVER (
PARTITION BY TRUNC(dt, 'MM')
ORDER BY dt, change DESC, ROWNUM
),
change,
SUM(change) OVER (
ORDER BY dt, change DESC, ROWNUM
)
FROM split_months
UNPIVOT ( dt FOR change IN ( Check_In AS 1, Check_Out AS -1 ) )
)
SELECT CASE
WHEN prev_dt IS NULL AND num_present - change = 0
THEN TRUNC(dt, 'MM')
ELSE dt
END AS start_free,
CASE
WHEN prev_dt IS NULL AND num_present - change = 0
| START_FREE | FREE_END | DAYS |
|---|---|---|
| 2023-08-01 00:00:00 | 2023-08-05 00:00:00 | 4 |
| 2023-08-27 00:00:00 | 2023-09-01 00:00:00 | 5 |
| 2023-09-01 00:00:00 | 2023-09-02 00:00:00 | 1 |
| 2023-09-06 00:00:00 | 2023-10-01 00:00:00 | 25 |
WITH find_overlaps (prev_free_start, free_end, free_start, next_free_end) AS (
SELECT LAG(check_out + 1) OVER (ORDER BY check_in),
check_in,
check_out + 1,
next_check_in
FROM table_name
MATCH_RECOGNIZE(
ORDER BY check_in, check_out DESC
MEASURES
FIRST(check_in) AS check_in,
MAX(check_out) AS check_out,
NEXT(check_in) AS next_check_in
PATTERN (overlap* non_overlap)
DEFINE
overlap AS MAX(check_out) >= NEXT(check_in)
)
),
unique_free_ranges (free_start, free_end) AS (
SELECT COALESCE(free_start, TRUNC(free_end , 'MM')),
COALESCE(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1))
FROM find_overlaps
UNPIVOT (
(free_start, free_end) FOR before_after IN (
(prev_free_start, free_end) AS 0,
(free_start, next_free_end) AS 1
)
)
WHERE before_after = 0 AND free_start IS NULL
OR before_after = 1
),
split_months (free_start, month_end, free_end) AS (
SELECT free_start,
LEAST(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1)),
free_end
FROM unique_free_ranges
UNION ALL
| MONTH | DAYS_FREE |
|---|---|
| 2023-08-01 00:00:00 | 9 |
| 2023-09-01 00:00:00 | 26 |
WITH find_overlaps (prev_free_start, free_end, free_start, next_free_end) AS (
SELECT LAG(check_out + 1) OVER (ORDER BY check_in),
check_in,
check_out + 1,
next_check_in
FROM table_name
MATCH_RECOGNIZE(
ORDER BY check_in, check_out DESC
MEASURES
FIRST(check_in) AS check_in,
MAX(check_out) AS check_out,
NEXT(check_in) AS next_check_in
PATTERN (overlap* non_overlap)
DEFINE
overlap AS MAX(check_out) >= NEXT(check_in)
)
),
unique_free_ranges (free_start, free_end) AS (
SELECT COALESCE(free_start, TRUNC(free_end , 'MM')),
COALESCE(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1))
FROM find_overlaps
UNPIVOT (
(free_start, free_end) FOR before_after IN (
(prev_free_start, free_end) AS 0,
(free_start, next_free_end) AS 1
)
)
WHERE before_after = 0 AND free_start IS NULL
OR before_after = 1
),
split_months (free_start, month_end, free_end) AS (
SELECT free_start,
LEAST(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1)),
free_end
FROM unique_free_ranges
UNION ALL
| FREE_START | FREE_END | DAYS_FREE |
|---|---|---|
| 2023-08-01 00:00:00 | 2023-08-05 00:00:00 | 4 |
| 2023-08-27 00:00:00 | 2023-09-01 00:00:00 | 5 |
| 2023-09-06 00:00:00 | 2023-10-01 00:00:00 | 25 |
| 2023-09-01 00:00:00 | 2023-09-02 00:00:00 | 1 |