I have a table showing subscriptions with a column for active date and a column for inactive date. I need a query which counts the number of active subscriptions for each specific date in a date range.

I'm struggling to add in a row for each date in my date range so that I can then compare with the other date columns in my table.

Example of my table

Example of the result I need

CodePudding user response：

SELECT 
type,
TO_DATE(date, 'DD-MM-YYYY') as date,
count(date) AS count_of_dates
FROM table
GROUP BY type, date
ORDER BY type

CodePudding user response：

You can UNPIVOT your two date columns and then use a PARTITIONed OUTER JOIN to a calendar:

WITH data (type, subscription_id, is_active, dt) AS (
  SELECT type, subscription_id, is_active, dt
  FROM   table_name
  UNPIVOT (
    dt FOR is_active IN (
      active_date AS 1,
      inactive_date AS 0
    )
  )
)
SELECT d.type,
       c.day,
       COUNT(CASE d.is_active WHEN 1 THEN 1 END) AS count_active,
       COUNT(CASE d.is_active WHEN 0 THEN 1 END) AS count_inactive
FROM   (
         SELECT TRUNC(min_dt)   LEVEL - 1 AS day
         FROM   (
           SELECT MIN(dt) AS min_dt,
                  MAX(dt) AS max_dt
           FROM   data
         )
         CONNECT BY TRUNC(min_dt)   LEVEL - 1 < max_dt
       ) c
       LEFT OUTER JOIN data d
       PARTITION BY (d.type)
       ON (c.day <= d.dt AND d.dt < c.day   1)
GROUP BY
       d.type,
       c.day
ORDER BY
       d.type,
       c.day

Which, for the sample data:

CREATE TABLE table_name (type, subscription_id, active_date, inactive_date) AS
SELECT 'A', 1, DATE '2022-11-01', NULL FROM DUAL UNION ALL
SELECT 'A', 2, DATE '2022-11-02', DATE '2022-11-10' FROM DUAL UNION ALL
SELECT 'A', 3, DATE '2022-11-03', NULL FROM DUAL UNION ALL
SELECT 'A', 4, DATE '2022-11-04', NULL FROM DUAL UNION ALL
SELECT 'B', 5, DATE '2022-11-05', NULL FROM DUAL UNION ALL
SELECT 'B', 6, DATE '2022-11-06', NULL FROM DUAL UNION ALL
SELECT 'B', 7, DATE '2022-11-07', DATE '2022-11-09' FROM DUAL UNION ALL
SELECT 'B', 8, DATE '2022-11-08', NULL FROM DUAL;

Outputs:

fiddle

CodePudding user response：

You can do it this way:

-- SAMPLE DATA
WITH
    tbl AS
        (
            Select 'A' "A_TYPE", 1 "SUBSCRIPTION_ID", To_Date('10.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual Union All
            Select 'A' "A_TYPE", 2 "SUBSCRIPTION_ID", To_Date('11.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('14.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
            Select 'A' "A_TYPE", 3 "SUBSCRIPTION_ID", To_Date('12.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('14.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
            Select 'B' "A_TYPE", 4 "SUBSCRIPTION_ID", To_Date('13.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual Union All
            Select 'B' "A_TYPE", 5 "SUBSCRIPTION_ID", To_Date('14.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", To_Date('18.10.2022', 'dd.mm.yyyy') "INACTIVE_DATE" From Dual Union All
            Select 'B' "A_TYPE", 6 "SUBSCRIPTION_ID", To_Date('15.10.2022', 'dd.mm.yyyy') "ACTIVE_DATE", Null "INACTIVE_DATE" From Dual 
        ),

CTE To generate the dates for A_TYPE: Here you can define range starting date (09.10.2022) and how many days you want the range to last (LEVEL <= 11)

    dates AS
        (
            Select Distinct
                t.A_TYPE "A_TYPE",
                d.RANGE_DATE "RANGE_DATE"
            From 
                (Select To_Date('09.10.2022', 'dd.mm.yyyy')   LEVEL - 1 "RANGE_DATE" From dual Connect By LEVEL <= 11) d
            Left Join
                tbl t ON(1 = 1)
            Order By 
                t.A_TYPE,
                d.RANGE_DATE
        )

And main SQL:

SELECT
    d.A_TYPE "A_TYPE",
    d.RANGE_DATE "RANGE_DATE",
    (Select Count(*) From tbl Where A_TYPE = d.A_TYPE And ACTIVE_DATE <= d.RANGE_DATE And Nvl(INACTIVE_DATE, To_Date('11.10.2062', 'dd.mm.yyyy')) > d.RANGE_DATE) "ACTIVE_COUNT"
FROM
    dates d

This is the result:

/*
A_TYPE RANGE_DATE ACTIVE_COUNT
------ ---------- ------------
A      09-OCT-22             0 
A      10-OCT-22             1 
A      11-OCT-22             2 
A      12-OCT-22             3 
A      13-OCT-22             3 
A      14-OCT-22             1 
A      15-OCT-22             1 
A      16-OCT-22             1 
A      17-OCT-22             1 
A      18-OCT-22             1 
A      19-OCT-22             1 
B      09-OCT-22             0 
B      10-OCT-22             0 
B      11-OCT-22             0 
B      12-OCT-22             0 
B      13-OCT-22             1 
B      14-OCT-22             2 
B      15-OCT-22             3 
B      16-OCT-22             3 
B      17-OCT-22             3 
B      18-OCT-22             2 
B      19-OCT-22             2
*/

Regards...