Solved

SQL 2008 Stored Procedure - multiple counts

Posted on 2011-02-15
2
538 Views
Last Modified: 2012-06-21
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?

Thx
0
Comment
Question by:BrightRaven
2 Comments
 
LVL 11

Accepted Solution

by:
jasonduan earned 500 total points
ID: 34899184
SELECT Office,
         SUM(C1) AS Count1,
         SUM(C2) AS Count2,
         SUM(C3) AS Count3
FROM
(
    SELECT Office,
         CASE WHEN DateDue <= GetDate() THEN 1 ELSE 0 END AS C1,
         CASE WHEN DateDue <= DateAdd(month, 1, GetDate()) THEN 1 ELSE 0 END AS C2,
         CASE WHEN DateDue <= DateAdd(month, 2, GetDate()) THEN 1 ELSE 0 END AS C3,
    FROM MyTable              
) T
GROUP BY Office
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34899563

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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now