需要具有动态数据范围计数的T枢轴。

Question

我需要在T的一个支点上得到帮助，但我做不到。

这些都是要求：

1. 需要将Startdate和EndDate定义为“yyyy”
2. 按月合计
3. 每个月只能计算一个"ClientID“(见2018-05年的"A”)。

我有这张带有样本数据的表格：

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

结果应该是这样：

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

请帮帮我！

Answer 1

这是你想要的吗？

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;

与您的结果的主要区别在于，这将返回0s而不是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;

您将得到以下结果

​

​