Solved

SQL 2008 Stored Procedure - multiple counts

Posted on 2011-02-15
2
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore 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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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