Modified Difficult One Table Query.

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)

Modified Difficult One Table Query.

Author

Topic

rotortorque
Starting Member

2 Posts

Posted - 2008-07-14 : 12:53:07

Hi everyone,
I posted this previously but my query selection has been modified. 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: RecordNo,ID, EventDate, OrderStatus. I was just informed that there are duplicate records in the table. Now my query has to query DISTINCT RecordNo and Max ID.

Here is an example of what the data in the table looks like:
RecordNo ID EventDate OrderStatus
1 10 7/5/2008 Opened
2 11 7/5/2008 Closed
3 12 7/5/2008 Closed
4 13 7/6/2008 Opened
4 14 7/6/2008 Opened
4 15 7/6/2008 Closed
1 16 7/7/2008 Opened
2 17 7/8/2008 Postponed
3 18 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 0 0 1
7/7/2008 1 0 0
7/8/2008 0 0 1

If you notice the status counted for 7/6/2008 is CLOSED because it select the MAX ID for that given record. Is this possible?

Thanks in advance
ROTORTORQUE

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-14 : 13:12:01

EventDate Opened Closed Postponed
7/5/2008 1 2 0
7/6/2008 0 0 1
7/7/2008 1 0 0
7/8/2008 0 0 1

Closed or postponed?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 13:13:57

Your output doesnt make much sense. How did you only calculate Postponed one for 7/8/2008 though MAX ID record had status closed?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-14 : 13:14:07

I can't follow how your results matches your data. These queries are what I was imagining you wanted, but they don't match your results. I'm confused.

; with a as (select *, row_number() over (partition by EventDate, RecordNo order by id desc) as row from ORDERS)
select * from a where row = 1 order by EventDate

; with a as (select *, row_number() over (partition by EventDate, RecordNo order by id desc) as row from ORDERS)
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 a where row = 1 group by EventDate order by EventDate

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-14 : 13:15:05

quote:
Originally posted by visakh16

Your output doesnt make much sense. How did you only calculate Postponed one for 7/8/2008 though MAX ID record had status closed?

Not just me then

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-14 : 13:22:33

Same comments as the Ryan and Visakh. But, here is a shot in the dark:

DECLARE @Orders TABLE (RecordNo INT, ID INT, EventDate DATETIME, OrderStatus VARCHAR(10))

INSERT @Orders
SELECT 1, 10, '7/5/2008', 'Opened'
UNION ALL SELECT 2, 11, '7/5/2008', 'Closed'
UNION ALL SELECT 3, 12, '7/5/2008', 'Closed'
UNION ALL SELECT 4, 13, '7/6/2008', 'Opened'
UNION ALL SELECT 4, 14, '7/6/2008', 'Opened'
UNION ALL SELECT 4, 15, '7/6/2008', 'Closed'
UNION ALL SELECT 1, 16, '7/7/2008', 'Opened'
UNION ALL SELECT 2, 17, '7/8/2008', 'Postponed'
UNION ALL SELECT 3, 18, '7/8/2008', 'Closed'

SELECT *
FROM
(
	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
	WHERE
		RecordNo < (SELECT MAX(RecordNo) FROM @Orders)
	GROUP BY
		EventDate

	UNION 

	SELECT
		MAX(EventDate),
		0,
		0,
		1
	FROM
		@Orders
	WHERE
		RecordNo = (SELECT MAX(RecordNo) FROM @Orders)
) AS T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 13:35:37

quote:
Originally posted by RyanRandall

quote:
Originally posted by visakh16

Your output doesnt make much sense. How did you only calculate Postponed one for 7/8/2008 though MAX ID record had status closed?
Not just me then

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Certainly not

. we're all confused.

Spetty
Starting Member

25 Posts

Posted - 2008-07-15 : 08:30:25

Is this what you are looking for?

select distinct
eventdate,
(select count(*) from events b where b.eventdate = a.eventdate and orderstatus = 'Opened') as Opened ,
(select count(*) from events b where b.eventdate = a.eventdate and orderstatus = 'Closed') as Closed ,
(select count(*) from events b where b.eventdate = a.eventdate and orderstatus = 'Postponed') as Postponed
from events a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:46:28

Not confused. He means DISTINCT per RecordNo AND EventDate.

DECLARE	@Sample TABLE (RecordNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9))

INSERT	@Sample
SELECT	1, 10, '7/5/2008', 'Opened' UNION ALL
SELECT	2, 11, '7/5/2008', 'Closed' UNION ALL
SELECT	3, 12, '7/5/2008', 'Closed' UNION ALL
SELECT	4, 13, '7/6/2008', 'Opened' UNION ALL
SELECT	4, 14, '7/6/2008', 'Opened' UNION ALL
SELECT	4, 15, '7/6/2008', 'Closed' UNION ALL
SELECT	1, 16, '7/7/2008', 'Opened' UNION ALL
SELECT	2, 17, '7/8/2008', 'Postponed' UNION ALL
SELECT	3, 18, '7/8/2008', 'Closed'

;WITH Yak (EventDate, OrderStatus, RecID)
AS (
	SELECT	EventDate,
		OrderStatus,
		ROW_NUMBER() OVER (PARTITION BY RecordNo, EventDate ORDER BY ID DESC)
	FROM	@Sample
)

SELECT	p.EventDate,
	p.Opened,
	p.Closed,
	p.Postponed
FROM	Yak
PIVOT	(
		COUNT(OrderStatus)
		FOR OrderStatus IN ([Opened], [Closed], [Postponed])
	) AS p
WHERE	p.RecID = 1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:48:49

Previous topic is here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106517

What confuses me is that original poster didn't provide full information from the beginning.
Now we all have spent double the time to solve his problem.

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