CODEWITHSUNDEEP
sqlt-sql
fenster
fenster

Reputation: 1859

Using a sub query in column list

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

7Days  14Days  28Days  
21     35      56

I know how to for each timepsan e.g. 7 days, but I do I capture all three in one query?

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

Upvotes: 4

Views: 3637

Answers (4)

jlnorsworthy
jlnorsworthy

Reputation: 3974

Also not pretty, but doesn't rely on subqueries (table/column names are from AdventureWorks). The case statement returns 1 if it falls within your criteria, 0 otherwise - then you just sum 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: Updated the datediff function - the way it was written, it would return a negative number (assuming modifieddate was in the past) causing all items to fall under the first case. Thanks to Andriy M for pointing that out

Upvotes: 4

Andriy M
Andriy M

Reputation: 77707

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

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]

Upvotes: 0

Brian Webster
Brian Webster

Reputation: 30865

It isn't the prettiest code in the world, but it does the trick. Try selecting 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

Upvotes: 0

Related Questions