在“天”范围内显示数据

Question

我有一个sql查询输出，如下所示

Customer  LastModifiedDate
A          1/12/2013
B          1/1/2015
C          1/28/2015     

现在我需要显示在不同日期范围内更新详细信息的客户计数，例如(30-60 days)、(61-90 Days)和90+（超过 90 天）例如，请参阅下面的输出

DaysRange      CustomersCount
30-60          1
61-90          1
90+            1

请帮助我实现上述输出

Answer 1

像这样的东西。

SELECT DaysRange=CASE
                   WHEN Datediff(DAY, LastModifiedDate, Getdate()) BETWEEN 30 AND 60 THEN '30-60'
                   WHEN Datediff(DAY, LastModifiedDate, Getdate()) BETWEEN 61 AND 90 THEN '61-90'
                   WHEN Datediff(DAY, LastModifiedDate, Getdate()) > 90 THEN '90+'
                 END,
       CustomersCount=Count(1)
FROM   yourtable
GROUP  BY CASE
            WHEN Datediff(DAY, LastModifiedDate, Getdate()) BETWEEN 30 AND 60 THEN '30-60'
            WHEN Datediff(DAY, LastModifiedDate, Getdate()) BETWEEN 61 AND 90 THEN '61-90'
            WHEN Datediff(DAY, LastModifiedDate, Getdate()) > 90 THEN '90+'
          END 

Answer 2

使用此查询：

SELECT COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) BETWEEN 30 AND 60 THEN 1 END) AS [30-60],
       COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) BETWEEN 61 AND 90 THEN 1 end) AS [61-90],
       COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) > 90 THEN 1 END) AS [90+]
   FROM mytable

生成以下输出：

30-60   61-90   90+
1       1       1

通过UNPIVOT我们得到所需的结果集：

SELECT DaysRange, CustomersCount
FROM (
   SELECT COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) BETWEEN 30 AND 60 THEN 1 END) AS [30-60],
          COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) BETWEEN  61 AND 90 THEN 1 END) AS [61-90],
          COUNT(CASE WHEN DATEDIFF(d, LastModifiedDate, getdate()) > 90 THEN 1 END) AS [90+]
   FROM mytable) p
UNPIVOT
   (CustomersCount FOR DaysRange IN ([30-60], [61-90], [90+])
) AS unpvt;

输出：

DaysRange   CustomersCount
30-60       1
61-90       1
90+         1