Need T-SQL pivot with dynamic date range counting occurrence

Question

I need help on a pivot in T-SQL which I can't make work.

These are the requirements:

1. Need to define a Startdate and EndDate as "yyyy-MM"
2. Aggregate on Month level
3. Each month only one "ClientID" must be counted (see "A" in 2018-05)

I have this table with sample data:

ClientID    Model   LastSyncTime_DW
---------------------------------------------
1           A       2018-09-26 00:00:00.000
2           A       2018-09-05 00:00:00.000
1           A       2018-08-19 00:00:00.000
1           A       2018-07-25 00:00:00.000
3           B       2018-07-03 00:00:00.000
1           A       2018-06-10 00:00:00.000
3           B       2018-06-07 00:00:00.000
8           A       2018-06-01 00:00:00.000
1           A       2018-05-31 00:00:00.000 (duplicate ClientID for this month - count only one)
3           B       2018-05-29 00:00:00.000
4           C       2018-05-26 00:00:00.000
5           D       2018-05-25 00:00:00.000
6           C       2018-05-24 00:00:00.000
1           A       2018-05-19 00:00:00.000 (duplicate ClientID for this month - count only one)
7           D       2018-05-12 00:00:00.000
8           A       2018-05-09 00:00:00.000
9           A       2018-05-05 00:00:00.000

The result should be like this:

Model   2018-09 2018-08 2018-07 2018-06 2018-5
-----------------------------------------------
A       2       1       1       2       3
B                       1       1       
C                                       2
D                                       2

Please help!

Answer 1

Is this what you want?

select model,
       count(distinct case when LastSyncTime_DW >= '2018-09-01' and LastSyncTime_DW < '2018-10-01' then ClientID end) as cnt_201809,
       count(distinct case when LastSyncTime_DW >= '2018-08-01' and LastSyncTime_DW < '2018-10-01' then ClientID end) as cnt_201808,
       . . .
from t
group by model;

The major difference from your results is that this returns 0s instead of NULLs.

Answer 2

    Create table #Pivot 
    (ClientID     int,Model  varchar(2),LastSyncTime_DW datetime)

    Insert into #Pivot Values   (1, 'A',    '2018-09-26'),
                        (2, 'A',    '2018-09-05'),
                        (1, 'A',    '2018-08-19'),
                        (1, 'A',    '2018-07-25'),
                        (3, 'B',    '2018-07-03'),
                        (1, 'A',    '2018-06-10'),
                        (3, 'B',    '2018-06-07'),
                        (8, 'A',    '2018-06-01'),
                        (1, 'A',    '2018-05-31'),
                        (3, 'B',    '2018-05-29'),
                        (4, 'C',    '2018-05-26'),
                        (5, 'D',    '2018-05-25'),
                        (6, 'C',    '2018-05-24'),
                        (1, 'A',    '2018-05-19'),
                        (7, 'D',    '2018-05-12'),
                        (8, 'A',    '2018-05-09'),
                        (9, 'A',    '2018-05-05')

Select * from #Pivot

  select model,
   count(distinct case when LastSyncTime_DW >= '2018-09-01' and LastSyncTime_DW < '2018-10-01' then ClientID end) as cnt_201809,
   count(distinct case when LastSyncTime_DW >= '2018-08-01' and LastSyncTime_DW < '2018-09-01' then ClientID end) as cnt_201808,
   count(distinct case when LastSyncTime_DW >= '2018-07-01' and LastSyncTime_DW < '2018-08-01' then ClientID end) as cnt_201807,
   count(distinct case when LastSyncTime_DW >= '2018-06-01' and LastSyncTime_DW < '2018-07-01' then ClientID end) as cnt_201806,
   count(distinct case when LastSyncTime_DW >= '2018-05-01' and LastSyncTime_DW < '2018-06-01' then ClientID end) as cnt_201805
 from #Pivot
 group by model;

You will get the Following Results