Difficult Single Table Query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difficult Single Table Query.

Author

Topic

rotortorque
Starting Member

2 Posts

Posted - 2008-07-14 : 01:04:04

Hi everyone,
I am new to SQL and I need assistance with what I consider a difficult query. I need to find a count of the orders that have been opened, closed, or postponed for a given date range.

The name of the table is ORDERS and the fields I need to work with are: EventDate, and OrderStatus.

Here is an example of what the data in the table looks like:
EventDate OrderStatus
7/5/2008 Opened
7/5/2008 Closed
7/5/2008 Closed
7/6/2008 Opened
7/7/2008 Opened
7/8/2008 Postponed
7/8/2008 Closed

Here is what the result needs to look like:
EventDate Opened Closed Postponed
7/5/2008 1 2 0
7/6/2008 1 0 0
7/7/2008 1 0 0
7/8/2008 0 0 1

I this possible?

Thanks in advance

ROTORTORQUE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 01:08:10

This is very much possible. just do like this.

SELECT EventDate,
SUM(CASE WHEN OrderStatus='Opened' THEN 1 ELSE 0 END) AS Opened,
SUM(CASE WHEN OrderStatus='Closed' THEN 1 ELSE 0 END) AS Closed,
SUM(CASE WHEN OrderStatus='Postponed' THEN 1 ELSE 0 END) AS Postponed 
FROM ORDERS
GROUP BY EventDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 03:40:50

[code]SELECT p.EventDate,
p.Opened,
p.Closed,
p.Postponed
FROM Orders AS o
PIVOT (
COUNT(OrderStatus)
FOR OrderStatus IN ([Opened], [Closed], [Postponed])
) AS p[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources