TSQL count 2 different columns by using number table

Question

I am trying to count 2 different date columns on a monthly interval by using a number table:

numbers table:

num
0
1
2
3

Originating Table (Example):

Gender	Subscription_Date	Cancel_Date
male	2023-01-01	2023-05-01
male	2023-01-05	2023-01-08
male	2023-01-05	2023-03-09
male	2023-02-01	2023-04-08
female	2023-01-05	2023-04-08
female	2023-01-07	NULL

Final output should be like:

ReportMonth	Gender	Subscriptions	Cancellations
2023-01-01	male	3	1
2023-01-01	female	2	0
2023-02-01	male	1	0
2023-02-01	female	0	0
2023-03-01	male	0	1
2023-03-01	female	0	0
2023-04-01	male	0	1
2023-04-01	female	0	1
2023-05-01	male	0	0
2023-05-01	female	1	0

First attempt:

-- first day of the current month, one year ago
DECLARE @StartDate date = DATEADD(year,-1,DATEADD(month,datediff(month,0,getdate()),0)) 

-- Start from StartDate, showing results for Subscriptions for each month
SELECT
   @StartDate AS StartDate, Numbers.Num, DATEADD(month, Numbers.Num, @StartDate) AS ReportMonth
   ,S.Gender
   ,COUNT(S.SubscriptionDate) AS Subscriptions
FROM Numbers
JOIN
OriginatingTable AS S ON DATEADD(month, Numbers.Num, @StartDate) = DATEADD(MONTH, DATEDIFF(MONTH, 0, S.SubscriptionDate), 0)
WHERE (Numbers.Num <= DATEDIFF(month, @StartDate, GETDATE())) 
GROUP BY Numbers.Num, DATEADD(month, Numbers.Num, @StartDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, S.Subscription_Date), 0), S.Gender
ORDER BY Numbers.Num

This works, but I fail to integrate the second column "Cancellations".

Answer 1

Define your driving table: Numbers Cartesian join Genders and for every row calculate counts as scalar subquery Something like

select n.StartDate, n.EndDate,g.gender,
 DATEADD(month, Numbers.Num, @StartDate) AS ReportMonth,
 (select count(*) from OriginatingTable o 
   where o.gender=g.gender
     and o.Subscription_Date between n.StartDate and n.EndDate) as SubscriptionsCount,
 (select count(*) from OriginatingTable o 
   where o.gender=g.gender
     and o.Cancel_Date between n.StartDate and n.EndDate) as CancellationsCount
from numbers n
cross join Genders g

Answer 2

Sergey,

thank you!

with a bit modification it works:

DECLARE @StartDate date = DATEADD(year,-1,DATEADD(month,datediff(month,0,getdate()),0)) --first day of current month, one year ago
DECLARE @Genders table (Gender varchar(10));
INSERT INTO @Genders values('male'),('female');

SELECT DATEADD(month, N.Num, @StartDate) AS ReportMonth -- monthly Counter
        , (select count (Subscription_Date) from Originating_Table o
            where DATEADD(month,N.Num,@StartDate)= DATEADD(MONTH, DATEDIFF(MONTH,0,o.Subscription_Date),0)
            and o.Gender = g.Gender
          ) as Subscriptions
        , g.Gender
        ,
        (select count (Cancel_Date) from Originating_Table o
            where DATEADD(month,N.Num,@StartDate)= DATEADD(MONTH, DATEDIFF(MONTH,0,o.Cancel_Date),0)
            and o.Gender = g.Gender
          ) as Cancellations
    FROM Numbers N
    CROSS JOIN @Genders g
    WHERE N.Num < datediff(MONTH,@Startdate, GETDATE())+1 -- Report up to current month
    ORDER BY ReportMonth