Link to home
Start Free TrialLog in
Avatar of BrightRaven
BrightRavenFlag for Jersey

asked on

SQL 2008 Stored Procedure - multiple counts

Appreciate some help;

Sql 2008 stored procedure or Select statement preferred.
Say I have a 2 column table. Field 1 = Office and Field 2 = DateDue
I wish to count all those records which are earlier than a date (for example lets say GetDate())
I need to group the counts on the Office field.
I need to have 3 separate counts for each office. Less then now, less then now +1 month, and less than now +2 months.

So the result would be a mini recordset

Office   Count1   Count2   Count3
1         24            20             0
2         12            23             2
3         0              10             34
4         34             8              0

Can this be done in a single select statement? Perhaps temp table SP perhaps?

Avatar of jasonduan
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lowfatspread

select office,[0] as now,[1] as next month,[2] as [2months]
 from (
select office,case when duedate < convert(char(8),getdate(),112)+' 23:59:59.997' then 0
                        when duedate < dateadd(m,+1,convert(char(8),getdate(),112)+' 23:59:59.997' then 1
     else 2 end as Due
 from yourtable
 where duedate <= dateadd(m,1,convert(char(8),getdate(),112)+' 23:59:59.997')
) as x
pivot (count(*) for due in ([0],[1],[2])) as pvt
order by office