How do I search and group by a date column into sets of duration ranges?

I have a table like this;

jobID - jobTitle - jobCreatedDate

I want to make it possible for visitors to search in Jobs table and list it by grouping like this and I want to do it with just one SQL query:

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

I tried Group By "jobCreatedDate" with no success and I couldn't understand the logic of the necessary query.

Here is an example what 开发者_如何学PythonI'm trying to do:

Thanks for help.

You need to find the date difference between today and the field JobCreated. Then based on the difference in the value days, you need to classify the output accordingly into various categories to meet your requirements.

I hope that is what you are looking for.

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

Screenshot shows sample data and the query output.

Use the DATEADD tsql method.

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

In this query @NumDays is a parameter representing the number of days to subtract from the current date. Make sure you pass a negative number to the query. If you are trying to create non inclusive ranges, you'll need to use two DATEADD calls passing two parameters to the query.

EDIT I misunderstood what you were trying to do. This may not be the most elegant solution, but you could accomplish your goal using a union query.

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()))