Total customer per reporting date without union-CodePudding

I would like to display to run this report where I show the total number of customers per reporting date. Here is a how I need the data to look like:

My original dataset look like this (please see query): In order to calculate the number of customers. I need to use the start and end date: if Start_Date>reporting_date and End_Date<=reporting_date then count as a customer.

I was able to develop a script, but it only gives me the total number of customers for only one reporting date.

select '2022-10-31' reporting_date, count(case when Start_Date>'2022-10-31' and End_Date<='2022-10-31' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

Is there a way to amend the code with cross-join or other workarounds to the total customers per reporting date without doing many unions

select '2022-10-31' reporting_date, count(case when Start_Date>'2022-10-31' and End_Date<='2022-10-31' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

UNION ALL 

select '2022-9-30' reporting_date, count(case when Start_Date>'2022-9-301' and End_Date<='2022-9-30' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

CodePudding user response：

It is possible to provide date ranges as a separate table/subquery, join to the actual data and perform grouping:

select s.start_d, s.end_d, COUNT(Customer_ID) AS total
FROM (SELECT '2022-10-31'::DATE, '2022-10-31'::DATE
      UNION SELECT '2022-09-30', '2022-09-30') 
    AS s(start_d, end_d)
LEFT JOIN (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','2021-10-31','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)
  ON a.Start_Date>s.start_d and a.End_Date<=s.end_d
GROUP BY s.start_d, s.end_d;

Output: