I need help on a pivot in T-SQL which I can't make work.
These are the requirements:
- Need to define a Startdate and EndDate as "yyyy-MM"
- Aggregate on Month level
- 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!