0

Here is my issue, Source DB is Azure MSSQL - I have a table that lists all the subscriptions sold. The Relevant columns are:

Account Number, Subscription Group, Purchase Date, Cancellation Date

Cancellation Date contains NULL values (for Subscriptions that have yet to be cancelled). An Account may have multiple subscriptions, across multiple subscription groups.

Sample data is:

Account number | Subscription group | Purchase date | Cancellation date |

1 | A | 2/4/2013 | 2/4/2014 |
1 | A | 2/4/2013 | NULL |
1 | B | 2/4/2013 | NULL |
2 | C | 5/7/2018 | 5/8/2020 |
3 | C | 20/3/2020 | NULL |
3 | C | 4/8/2020 | NULL |

Etc.

I am looking to count the number of Subscription Groups for each customer and for each month - so the output would look something like this:

Customer | Count of A | Count of B | Count of C | Month/Year |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

I can write the query for a given month:

select t.[account number],
COUNT(CASE WHEN t.[Subscription group] = A then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN t.[Subscription group] = B then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN t.[Subscription group] = C then 1 ELSE NULL END) as "Count of C",
'09/2020' as 'Month/Year'
from table t
where (t.[Purchase Date] <= '30/09/2020' and (t.[Cancellation Date] is null or t.[Cancellation Date] > '01/10/2020') )
Group by t.[Account Number], [Month/Year]

However, I'm hoping someone can help me modify the above so it will run for All months (based on MIN from Purchase date and MAX on Cancellation date)

1
  • Please provide more information and data sample with the expected result. Right now, your sample doesn't match your expecting so it makes it confusing. Also, specify what you expect for the date grouping (you specified 2 date fields for only one grouping) – Dominique Boucher Oct 29 '20 at 16:13
0

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) < @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) > 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 "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.