Using an auxiliary query in a column list

Question

I would like to create a query that would calculate how many records were created in the last 7, 14 and 28 days. My result will return something like:

7Days 14Days 28Days 21 35 56 

I know how for every timessan, for example. 7 days, but I take all three in one request?

 select count(*) from Mytable where Created > DATEADD(day,-8, getdate()) 

Answer 1

Also not very, but not reliant on subqueries (table and column names are from AdventureWorks). The case statement returns 1 if it meets your criteria, 0 otherwise - then you simply summarize the results:

 select sum(case when datediff(day, modifieddate, getdate()) <= 7 then 1 else 0 end) as '7days', sum(case when datediff(day, modifieddate, getdate()) > 7 and datediff(day, modifieddate, getdate()) <= 14 then 1 else 0 end) as '14days', sum(case when datediff(day, modifieddate, getdate()) > 14 and datediff(day, modifieddate, getdate()) <= 28 then 1 else 0 end) as '28days' from sales.salesorderdetail 

Edit: The datiff function has been updated - the way it was written, it will return a negative number (provided that modifieddate was in the past), as a result of which all elements fall under the first case. Thanks to Andriy M for pointing out that

Answer 2

This is not the most beautiful code in the world, but it does the trick. Try choosing from three subqueries, one for each range.

 select * from (select COUNT(*) as Cnt from Log_UrlRewrites where CreateDate >= DATEADD(day, -7, getdate())) as Seven inner join (select COUNT(*) as Cnt from Log_UrlRewrites where CreateDate >= DATEADD(day, -14, getdate())) as fourteen on 1 = 1 inner join (select COUNT(*) as Cnt from Log_UrlRewrites where CreateDate >= DATEADD(day, -28, getdate())) as twentyeight on 1 = 1 

Answer 3

 select ( select count(*) from Mytable where Created > DATEADD(day,-8, getdate()) ) as [7Days], ( select count(*) from Mytable where Created > DATEADD(day,-15, getdate()) ) as [14Days], ( select count(*) from Mytable where Created > DATEADD(day,-29, getdate()) ) as [28Days] 

Answer 4

 SELECT [7Days] = COUNT(CASE UtmostRange WHEN 7 THEN 1 END), [14Days] = COUNT(CASE UtmostRange WHEN 14 THEN 1 END), [28Days] = COUNT(CASE UtmostRange WHEN 28 THEN 1 END) FROM ( SELECT *, UtmostRange = CASE WHEN Created > DATEADD(day, -8, GETDATE()) THEN 7 WHEN Created > DATEADD(day, -15, GETDATE()) THEN 14 WHEN Created > DATEADD(day, -29, GETDATE()) THEN 28 END FROM Mytable ) s