Counting Grouped records, using start date and end date with NULLs
I ended up doing this using a Stored Procedure, creating a Temp Table:
DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')      
         ,@EndDate  = (select max([Cancellation date]) from Table);


;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) &lt; @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

Then doing a Loop through that Table and populating an output table:
Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) &gt; 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as &quot;Count of A&quot;,
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as &quot;Count of B&quot;,
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as &quot;Count of C&quot;,
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] &lt;= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] &gt; @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable