I have data in my table this way
| Start_Dt | End_Dt | Status |
|---|---|---|
| 1Jan2021 | 24Apr2021 | Active |
| 25Apr2021 | 10Jun2021 | Inactive |
| 11Jun2021 | 15Jan2022 | Active |
I want to calculate Active days monthly. In this case Active monthly days will be
Total Active Days = (31/31 28/28 31/31 24/30 20/30 31/31 31/31 30/30 31/31 30/30 31/31) / 12 = 0.8722
How do I derive the active days for each month in 1 year?
CodePudding user response：
Use a row generator to generate all the months and join to your table and aggregate:
WITH months (month) AS (
SELECT ADD_MONTHS(DATE '2021-01-01', LEVEL - 1)
FROM DUAL
CONNECT BY LEVEL <= 12
)
SELECT SUM(
( LEAST(ADD_MONTHS(m.month, 1), end_dt 1)
- GREATEST(m.month, start_dt) )
/ (ADD_MONTHS(m.month, 1) - m.month)
) / 12 AS avg_active_days_per_month_2021
FROM table_name t
INNER JOIN months m
ON ( t.start_dt < ADD_MONTHS(m.month, 1)
AND t.end_dt > m.month )
WHERE t.status = 'Active'
Which, for the sample data:
CREATE TABLE table_name (Start_Dt, End_Dt, Status) AS
SELECT DATE '2021-01-01', DATE '2021-04-24', 'Active' FROM DUAL UNION ALL
SELECT DATE '2021-04-25', DATE '2021-06-10', 'Inactive' FROM DUAL UNION ALL
SELECT DATE '2021-06-11', DATE '2022-01-15', 'Active' FROM DUAL;
Outputs:
AVG_ACTIVE_DAYS_PER_MONTH_2021 .8722222222222222222222222222222222222225
db<>fiddle here
CodePudding user response：
Here is a way to get monthly active days even when there is more then one period of inactivity. In the WITH clause there are just the sample data in a table with two periods of inactivity (to make it more complicated). The cte inact separates inactivity periods, and cte months generates months with starting dates and number of days per month. In the main Select, all the job is done using CASE in the subquery calculating active days out of dates of inactivities and number of days per month. Hope this could be of help to you. Regards...
WITH
tbl AS
(
SELECT DATE '2021-01-01' "START_DATE", DATE '2021-03-24' "END_DATE", 'Active' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-03-25' "START_DATE", DATE '2021-04-10' "END_DATE", 'Inactive' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-04-11' "START_DATE", DATE '2021-07-18' "END_DATE", 'Active' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-07-19' "START_DATE", DATE '2021-07-30' "END_DATE", 'Inactive' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-07-31' "START_DATE", DATE '2022-01-25' "END_DATE", 'Active' "STATUS" FROM DUAL
),
-- ------------------------------------------------------------------------
inact AS
(
SELECT
Count(START_DATE) OVER(ORDER BY START_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "NUM_OF_INACTIVITIES",
START_DATE "INACT_START",
END_DATE "INACT_END"
FROM
tbl
WHERE
To_Char(START_DATE, 'yyyy') = '2021' And
STATUS = 'Inactive'
),
months AS
(
SELECT
LPAD(To_Char(LEVEL), 2, '0') "WRK_MONTH",
To_Date('2021-' || LPAD(To_Char(LEVEL), 2, '0') || '-01', 'yyyy-mm-dd') "MONTH_START",
To_Number(To_Char(LAST_DAY(To_Date('2021-' || LPAD(To_Char(LEVEL), 2, '0') || '-01', 'yyyy-mm-dd')), 'dd')) "MONTH_NUM_OF_DAYS",
i.INACT_START,
i.INACT_END
FROM
DUAL d
INNER JOIN
inact i ON(1=1)
CONNECT BY LEVEL <= 12
)
-- --------------------------------------------------------------------------
SELECT
x.MONTH,
Min(ACTIVE_DAYS) "ACTIVE_DAYS"
FROM
(
SELECT
m.WRK_MONTH "MONTH",
m.MONTH_NUM_OF_DAYS "MONTH_NUM_OF_DAYS",
CASE
WHEN m.WRK_MONTH < To_Char(m.INACT_START, 'mm') THEN To_Char(m.MONTH_NUM_OF_DAYS) || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
WHEN m.WRK_MONTH = To_Char(m.INACT_START, 'mm') And m.WRK_MONTH < To_Char(m.INACT_END, 'mm') THEN To_Char(m.INACT_START - 1, 'dd') || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
WHEN m.WRK_MONTH > To_Char(m.INACT_START, 'mm') And m.WRK_MONTH < To_Char(m.INACT_END, 'mm') THEN '0' || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
WHEN m.WRK_MONTH > To_Char(m.INACT_START, 'mm') And m.WRK_MONTH = To_Char(m.INACT_END, 'mm') THEN To_Char(m.MONTH_NUM_OF_DAYS - To_Number(To_Char(m.INACT_END, 'dd'))) || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
WHEN m.WRK_MONTH = To_Char(m.INACT_START, 'mm') And m.WRK_MONTH = To_Char(m.INACT_END, 'mm') THEN To_Char(m.MONTH_NUM_OF_DAYS - (To_Number(To_Char(m.INACT_END, 'dd')) - To_Number(To_Char(m.INACT_START, 'dd')))) || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
WHEN m.WRK_MONTH > To_Char(m.INACT_END, 'mm') THEN To_Char(m.MONTH_NUM_OF_DAYS) || '/' || To_Char(m.MONTH_NUM_OF_DAYS)
ELSE Null
END "ACTIVE_DAYS"
FROM
months m
) x
INNER JOIN
inact i ON(1 = 1)
GROUP BY
x.MONTH
ORDER BY
x.MONTH
--
-- R e s u l t
--
-- MONTH ACTIVE_DAYS
-- ----- ---------------------------------------------------------------------------------
-- 01 31/31
-- 02 28/28
-- 03 24/31
-- 04 20/30
-- 05 31/31
-- 06 30/30
-- 07 20/31
-- 08 31/31
-- 09 30/30
-- 10 31/31
-- 11 30/30
-- 12 31/31
And with three periods of inactivity...
WITH
tbl AS
(
SELECT DATE '2021-01-01' "START_DATE", DATE '2021-03-24' "END_DATE", 'Active' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-03-25' "START_DATE", DATE '2021-04-10' "END_DATE", 'Inactive' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-04-11' "START_DATE", DATE '2021-07-18' "END_DATE", 'Active' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-07-19' "START_DATE", DATE '2021-07-30' "END_DATE", 'Inactive' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-07-31' "START_DATE", DATE '2021-08-25' "END_DATE", 'Active' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-08-26' "START_DATE", DATE '2021-11-03' "END_DATE", 'Inactive' "STATUS" FROM DUAL UNION ALL
SELECT DATE '2021-11-04' "START_DATE", DATE '2022-08-25' "END_DATE", 'Active' "STATUS" FROM DUAL
)
--
-- R e s u l t
--
-- MONTH ACTIVE_DAYS
-- ----- ---------------------------------------------------------------------------------
-- 01 31/31
-- 02 28/28
-- 03 24/31
-- 04 20/30
-- 05 31/31
-- 06 30/30
-- 07 20/31
-- 08 25/31
-- 09 0/30
-- 10 0/31
-- 11 27/30
-- 12 31/31