如何按日期列搜索和分组为多组持续时间范围？

Question

我有一张这样的桌子；

jobID - jobTitle - jobCreatedDate

我想让访问者可以在Job表中进行搜索，并像这样按分组列出它，我只想用一个SQL查询来做到这一点：

Date Added
- AnyTime (20)
- 1 day ago (5)
- 2-7 days ago (2)
- 8-14 days ago (0)
- 15-30 days ago (7)

我尝试了Group By "jobCreatedDate“，但没有成功，我不能理解必要查询的逻辑。

下面是我想要做的一个例子：

​

​

谢谢你的帮助。

Answer 1

您需要找出today和字段JobCreated之间的日期差异。然后根据值天数的差异，您需要将输出相应地分类到不同的类别中，以满足您的需求。

我希望这就是你要找的。

SELECT  SUM(1)  AS [AnyTime]
    ,   SUM(CASE WHEN DayCount = 1                  THEN 1 ELSE 0 END)  AS [1 day ago]
    ,   SUM(CASE WHEN DayCount BETWEEN 2 AND 7      THEN 1 ELSE 0 END)  AS [2-7 days ago]
    ,   SUM(CASE WHEN DayCount BETWEEN 8 AND 14     THEN 1 ELSE 0 END)  AS [8-14 days ago]
    ,   SUM(CASE WHEN DayCount BETWEEN 15 AND 30    THEN 1 ELSE 0 END)  AS [15-30 days ago]
FROM    
(
    SELECT      JobID
            ,   DATEDIFF(d, JobCreatedDate, GETDATE()) AS DayCount
    FROM        dbo.Jobs
) Jobs

屏幕截图显示了示例数据和查询输出。

​

​

Answer 2

使用DATEADD tsql方法。

Select ...
From [Table]
Where JobCreatedDate between DATEADD(dd, @NumDays, GetDate()) and GetDate()

在这个查询中，@NumDays是一个参数，表示从当前日期减去的天数。确保将负数传递给查询。如果您试图创建非包含范围，则需要使用两个DATEADD调用，并将两个参数传递给查询。

我误解了你想要做的事情。这可能不是最优雅的解决方案，但是您可以使用联合查询来实现您的目标。

Select sum(OneDay) as OneDay, sum(SevenDays) as SevenDays
From
 (select 1 as OneDay, 0 as SevenDays
  From [table]
  Where JobCreatedDate between DATEADD(dd, -1, GetDate()) and GetDate())
  UNION
 (select 0 as OneDay, 1 as SevenDays
  From[table]
  Where JobCreatedDate between DATEADD(dd, -7, GetDate()) and DATEADD(dd, -2, GetDate()))