Sample Data:
| date1 | date2 | fiscal mon | days diff |
|---|---|---|---|
| 22-10-21 | 29-10-21 | Oct21 | 8 |
| 30-10-21 | 26-11-21 | Nov21 | 28 |
| 27-11-21 | 24-12-21 | Dec21 | 28 |
| 25-12-21 | 28-01-22 | Jan22 | 35 |
| 29-01-22 | 25-02-22 | Feb22 | 28 |
| 26-02-22 | 25-03-22 | Mar22 | 28 |
| 26-03-22 | 29-04-22 | Apr22 | 35 |
| 30-04-22 | 27-05-22 | May22 | 28 |
| 28-05-22 | 24-06-22 | Jun22 | 28 |
| 25-06-22 | 29-07-22 | Jul22 | 35 |
| 30-07-22 | 13-08-22 | Aug22 | 15 |
Required Output:
| Month | Oct21 | Nov21 | Dec21 | Jan22 | Feb22 | Mar22 | Apr22 | May22 | Jun22 | Jul22 | Aug22 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Bkt90 | 8 | 28 | 28 | 26 | |||||||
| Bkt90-180 | 9 | 28 | 28 | 25 | |||||||
| Bkt180 | 10 | 28 | 28 | 35 | 15 |
CodePudding user response：
With some transformations it could be done like below:
- Your Sample Data
WITH
tbl_1 AS
(
Select To_Date('22-10-2021', 'dd-mm-yyyy') "DATE_1", To_Date('29-10-2021', 'dd-mm-yyyy') "DATE_2", 'Oct21' "MON", 8 "DAY_DIFF" From Dual Union All
Select To_Date('30-10-2021', 'dd-mm-yyyy') "DATE_1", To_Date('26-11-2021', 'dd-mm-yyyy') "DATE_2", 'Nov21' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('27-11-2021', 'dd-mm-yyyy') "DATE_1", To_Date('24-12-2021', 'dd-mm-yyyy') "DATE_2", 'Dec21' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('25-12-2021', 'dd-mm-yyyy') "DATE_1", To_Date('28-01-2022', 'dd-mm-yyyy') "DATE_2", 'Jan22' "MON", 35 "DAY_DIFF" From Dual Union All
Select To_Date('29-01-2022', 'dd-mm-yyyy') "DATE_1", To_Date('25-05-2022', 'dd-mm-yyyy') "DATE_2", 'Feb22' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('26-02-2022', 'dd-mm-yyyy') "DATE_1", To_Date('25-03-2022', 'dd-mm-yyyy') "DATE_2", 'Mar22' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('26-03-2022', 'dd-mm-yyyy') "DATE_1", To_Date('29-04-2022', 'dd-mm-yyyy') "DATE_2", 'Apr22' "MON", 35 "DAY_DIFF" From Dual Union All
Select To_Date('30-04-2022', 'dd-mm-yyyy') "DATE_1", To_Date('27-05-2022', 'dd-mm-yyyy') "DATE_2", 'May22' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('28-05-2022', 'dd-mm-yyyy') "DATE_1", To_Date('24-06-2022', 'dd-mm-yyyy') "DATE_2", 'Jun22' "MON", 28 "DAY_DIFF" From Dual Union All
Select To_Date('25-06-2022', 'dd-mm-yyyy') "DATE_1", To_Date('29-07-2022', 'dd-mm-yyyy') "DATE_2", 'Jul22' "MON", 35 "DAY_DIFF" From Dual Union All
Select To_Date('30-07-2022', 'dd-mm-yyyy') "DATE_1", To_Date('13-08-2022', 'dd-mm-yyyy') "DATE_2", 'Aug22' "MON", 15 "DAY_DIFF" From Dual
),
- First Transformation (CTE transformed)
- 1st grouping (STEP)
- Cumulativ Diff
- Days Diff limitation and over the limits
transformed AS
(
SELECT
DATE_1,
DATE_2,
MON,
DAY_DIFF,
FLOOR(Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 90) "STEP",
Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "CUM_DIFF",
CASE
WHEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 180
THEN DAY_DIFF - (Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 180)
WHEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 90
THEN DAY_DIFF - (Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 90)
ELSE 0
END "DAYS_LIMIT",
CASE
WHEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 180
THEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 180
WHEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 90
THEN Sum(DAY_DIFF) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 90
ELSE 0
END "DAYS_OVER"
FROM
tbl_1
),
- Second Transformation (CTE buckets)
buckets AS
(
SELECT
DATE_1 "DATE_1",
DATE_2 "DATE_2",
MON "MON",
DAY_DIFF "DAY_DIFF",
CUM_DIFF "CUMM_DIFF",
CUM_DIFF * (STEP - FIRST_VALUE(STEP) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) "CUM_DIFF_STEP",
STEP "STEP",
CASE WHEN DAYS_LIMIT <= 0 THEN DAY_DIFF ELSE DAYS_LIMIT END "DAYS_LIMIT",
FIRST_VALUE(STEP) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) "CHG",
CASE
WHEN CUM_DIFF * (STEP - FIRST_VALUE(STEP) OVER(PARTITION BY 1 ORDER BY DATE_1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) = 0
THEN 0
ELSE
DAYS_OVER
END "DAYS_OVER"
FROM
transformed
ORDER BY DATE_1
),
So far the result is:
-- DATE_1 DATE_2 MON DAY_DIFF CUMM_DIFF CUM_DIFF_STEP STEP DAYS_LIMIT CHG DAYS_OVER
-- --------- --------- ----- ---------- ---------- ------------- ---------- ---------- ---------- ----------
-- 22-OCT-21 29-OCT-21 Oct21 8 8 0 0 8 0 0
-- 30-OCT-21 26-NOV-21 Nov21 28 36 0 0 28 0 0
-- 27-NOV-21 24-DEC-21 Dec21 28 64 0 0 28 0 0
-- 25-DEC-21 28-JAN-22 Jan22 35 99 99 1 26 0 9
-- 29-JAN-22 25-MAY-22 Feb22 28 127 0 1 28 1 0
-- 26-FEB-22 25-MAR-22 Mar22 28 155 0 1 28 1 0
-- 26-MAR-22 29-APR-22 Apr22 35 190 190 2 25 1 10
-- 30-APR-22 27-MAY-22 May22 28 218 0 2 28 2 0
-- 28-MAY-22 24-JUN-22 Jun22 28 246 0 2 28 2 0
-- 25-JUN-22 29-JUL-22 Jul22 35 281 281 3 35 2 101
-- 30-JUL-22 13-AUG-22 Aug22 15 296 0 3 15 3 0
- Union All to create additional rows for overlapings (CTE buckets_all)
buckets_all AS
(
SELECT BKT, MON, DAYS_MON
FROM
(
SELECT
DATE_1 "DATE_1",
CASE
WHEN CHG = 0 THEN 'Bkt90'
WHEN CHG = 1 THEN 'Bkt180'
ELSE
'Bkt180 '
END "BKT",
MON "MON",
DAYS_LIMIT "DAYS_MON",
CHG "CHG"
FROM
buckets
UNION ALL
SELECT
DATE_1 "DATE_1",
CASE
WHEN CHG = 0 THEN 'Bkt180'
WHEN CHG = 1 THEN 'Bkt180 '
ELSE
'xxx'
END "BKT",
MON "MON",
DAYS_OVER "DAYS_MON",
CHG 1 "CHG"
FROM
buckets
WHERE
DAYS_OVER > 0 And
CHG <= 1
ORDER BY DATE_1, CHG
)
)
Now we have the result that we need for final SQL (Pivoting)
-- BKT MON DAYS_MON
-- ------- ----- ----------
-- Bkt90 Oct21 8
-- Bkt90 Nov21 28
-- Bkt90 Dec21 28
-- Bkt90 Jan22 26
-- Bkt180 Jan22 9
-- Bkt180 Feb22 28
-- Bkt180 Mar22 28
-- Bkt180 Apr22 25
-- Bkt180 Apr22 10
-- Bkt180 May22 28
-- Bkt180 Jun22 28
-- Bkt180 Jul22 35
-- Bkt180 Aug22 15
- Main SQL Pivoting the data
SELECT
*
FROM
buckets_all
PIVOT
(
Max(DAYS_MON)
FOR MON IN('Oct21' "Oct21", 'Nov21' "Nov21", 'Dec21' "Dec21", 'Jan22' "Jan22", 'Feb22' "Feb22", 'Mar22' "Mar22", 'Apr22' "Apr22", 'May22' "May22", 'Jun22' "Jun22", 'Jul22' "Jul22", 'Aug22' "Aug22")
)
ORDER BY
CASE BKT
WHEN 'Bkt90' THEN 0
WHEN 'Bkt180' THEN 1
WHEN 'Bkt180 ' THEN 2
END
R e s u l t
-- BKT Oct21 Nov21 Dec21 Jan22 Feb22 Mar22 Apr22 May22 Jun22 Jul22 Aug22
-- ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
-- Bkt90 8 28 28 26
-- Bkt180 9 28 28 25
-- Bkt180 10 28 28 35 15