Running a select statement for each case

Question

I'm a complete SQL novice. I want to display a simple grid.....

1. Sign up......>30.....30-60.....60-90.....>90.....Total
2. Feb...............4..........30........... 6 ......... 0 .......40
3. Mar ............. 0 .........11 ...........1 ..........4 .......16
4. Apr
5. May etc
6. Jun etc

Where total represents the total of number of customer sign ups per month, and where the likes of '30-60' represents the number sign ups on the date 30-60 days since today's date

The query I am using is as follows......

SELECT case MONTH(Datecreated)
when   2 then 'Febrary'
when   3 then 'March'
when   4 then 'April'
when   5 then 'May'
when   6 then 'June'
end as 'Signup Month',


(select count(*) from WS_USER_DETAILS where datediff(day, datecreated, GETUTCDATE()) < 30 )  as  '<= 30 days',
(select count(*) from WS_USER_DETAILS where datediff(day, datecreated, GETUTCDATE()) between 30 and 60) as  '<= 60 days',
(select count(*) from WS_USER_DETAILS where datediff(day, datecreated, GETUTCDATE()) between 60 and 90) as  '<= 90 days',
(select count(*) from WS_USER_DETAILS where datediff(day, datecreated, GETUTCDATE()) >90) as  '<= 120 days', 

count(userid) AS 'Total' FROM WS_USER_DETAILS


group by MONTH(Datecreated)

The problem I have is.... I want to run the select statements for each month. However it populates all months. For example......

1. Sign up......>30.....30-60.....60-90.....>90.....Total
2. Feb...............0..........11........... 1 ......... 4 .......40
3. Mar ............. 0 .........11 ...........1 ..........4 .......16

If possible can someone advise me on how to run the select statements for each month? Thanks

Answer 1

I think something like this would fit your needs better. You can use Sum Function and a case statement to work like a count if.

    SELECT
    DATENAME(MM,Datecreated), --This Function automatically gets the name of the month

    --Here is where you can use case statements like a count if. 
    SUM(CASE WHEN DATEDIFF(dd, datecreated, GETUTCDATE()) < 30 THEN 1 ELSE 0 END) '<= 30 days',
    SUM(CASE WHEN DATEDIFF(dd, datecreated, GETUTCDATE()) < 60 THEN 1 ELSE 0 END) '<= 60 days',
    SUM(CASE WHEN DATEDIFF(dd, datecreated, GETUTCDATE()) < 90 THEN 1 ELSE 0 END) '<= 90 days',
    SUM(CASE WHEN DATEDIFF(dd, datecreated, GETUTCDATE()) < 120 THEN 1 ELSE 0 END) '<= 120 days'

    FROM WS_USER_DETAILS

    GROUP BY Datepart(MM, Datecreated)

Sources:

1. http://msdn.microsoft.com/en-us/library/ms189794.aspx
2. http://msdn.microsoft.com/en-us/library/ms174420.aspx
3. http://msdn.microsoft.com/en-us/library/ms174420.aspx

Answer 2

First select the time span per record, then group and count. Be careful not to have overlapping ranges.

select
  monthname,
  isnull(sum(lessthan30),0) as lt30,
  isnull(sum(between30and59),0) as btw30a59,
  isnull(sum(between60and89),0) as btw60a89,
  isnull(sum(between90and119),0) as btw90a119,
  isnull(sum(morethan119),0) as mt119,
  isnull(sum(lessthan30),0) + isnull(sum(between30and59),0) + isnull(sum(between60and89),0) + isnull(sum(between90and119),0) + isnull(sum(morethan119),0) as total
from
(
  select
    month(datecreated) as mon,
    datename(month,datecreated) as monthname,
    case when datediff(day, datecreated, GETUTCDATE()) < 30 then 1 else 0 end as lessthan30,
    case when datediff(day, datecreated, GETUTCDATE()) between 30 and 59 then 1 else 0 end as between30and59,
    case when datediff(day, datecreated, GETUTCDATE()) between 60 and 89 then 1 else 0 end as between60and89,
    case when datediff(day, datecreated, GETUTCDATE()) between 90 and 119 then 1 else 0 end as between90and119,
    case when datediff(day, datecreated, GETUTCDATE()) >= 120 then 1 else 0 end as morethan119
  from WS_USER_DETAILS
) as dummy
group by monthname, mon
order by mon;

Answer 3

Based on @ThorstenKettner but adding performance by removing function calls and adding sergability

declare @today date
declare @_30 date
declare @_31 date
declare @_59 date
declare @_60 date
declare @_89 date
declare @_90 date
declare @_119 date
declare @_120 date


set @today = GETUTCDATE()
set @_30 = datediff(day, @today, 30)
set @_31 = datediff(day, @today, 31)
set @_59 = datediff(day, @today, 59)
set @_60 = datediff(day, @today, 60)
set @_89 = datediff(day, @today, 89)
set @_90 = datediff(day, @today, 90)
set @_119 = datediff(day, @today, 119)
set @_120 = datediff(day, @today, 120)


select
  monthname,
  isnull(sum(lessthan30),0) as lt30,
  isnull(sum(between30and59),0) as btw30a59,
  isnull(sum(between60and89),0) as btw60a89,
  isnull(sum(between90and119),0) as btw90a119,
  isnull(sum(morethan119),0) as mt119,
  isnull(sum(lessthan30),0) + isnull(sum(between30and59),0) + isnull(sum(between60and89),0) + isnull(sum(between90and119),0) + isnull(sum(morethan119),0) as total
from
(
  select
    month(datecreated) as mon,
    datename(month,datecreated) as monthname,
    case when datecreated < @_30 then 1 else 0 end as lessthan30,
    case when datecreated between @_30 and @_59 then 1 else 0 end as between30and59,
    case when datecreated  between @_60 and @_89 then 1 else 0 end as between60and89,
    case when datecreated  between @_90 and @_119 then 1 else 0 end as between90and119,
    case when datecreated  >= @_120 then 1 else 0 end as morethan119
  from WS_USER_DETAILS 
)as dummy
group by monthname, mon
order by mon;