Distinct queries???? - 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 2000 Forums

SQL Server Development (2000)

Distinct queries????

Author

Topic

vmclear04
Starting Member

2 Posts

Posted - 2004-08-24 : 13:40:22

Hi everyone,

First of all, thx to all of you out there for providing so much help to other people.

I have been struggling with this problem for the past few days ...

This is what my report looks like at this point:

ID OrderNum Status

Student1 100 Completed
Student1 200 Processing
Student1 300 Ignore

Student2 100 Completed
Student2 400 Working
Student2 200 NotAvailabe

Student3 600 Processing
Student3 200 Working
Student3 300 Working

Student4 400 Completed
Student4 200 Cancelled
Student4 300 Skip

The new report should look like this:

Student1 200 Processing
Student2 400 Working
Student3 200 Working
Student4 400 Completed

In other words, here are the conditions/requirements for the new report:

1) If a record has status = "Processing" then
display that record(Student1)
2) If a record has status = "Working" then
display the record that record (student2)
3) If a record has status = "Processing" and "Working" then
display either Processing or Working, whatever
has the lowest OrderNum (Student3)
4) If a record is not "Processing" nor "Working" then
display the record that has max OrderNum (Student4)

The hard part is that each student has multiple status on their products....

Any input is greatly appreciated.
Vicki

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-24 : 13:50:53

Hi Vicky...I think your result set is incorrect...

What school do you go to?

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-24 : 13:51:02

does this help??


Declare @myTable table (Id varchar(100), OrderNum int, Status varchar(100))

Insert Into @myTable Values('Student1', 100, 'Completed')
Insert Into @myTable Values('Student1', 200, 'Processing')
Insert Into @myTable Values('Student1', 300, 'Ignore')
Insert Into @myTable Values('Student2', 100, 'Completed')
Insert Into @myTable Values('Student2', 400, 'Working')
Insert Into @myTable Values('Student2', 200, 'NotAvailable')
Insert Into @myTable Values('Student3', 600, 'Processing')
Insert Into @myTable Values('Student3', 200, 'Working')
Insert Into @myTable Values('Student3', 300, 'Working')
Insert Into @myTable Values('Student4', 400, 'Completed')
Insert Into @myTable Values('Student4', 200, 'Cancelled')
Insert Into @myTable Values('Student4', 300, 'Skip')


Select 
	id,
	OrderNum = case 
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working')) in (1,2) then
				(Select top 1 OrderNum From @myTable where id = A.id and Status in ('Processing','Working') Order By OrderNum)
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working'))=0 then
				(Select top 1 OrderNum From @myTable where id = A.id Order By OrderNum Desc)
			End,
	Status = case 
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working')) in (1,2) then
				(Select top 1 Status From @myTable where id = A.id and Status in ('Processing','Working') Order By OrderNum)
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working'))=0 then
				(Select top 1 Status From @myTable where id = A.id Order By OrderNum Desc)
			End
From (Select Distinct id From @myTable) as A

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-24 : 13:56:18

quote:
Originally posted by X002548

Hi Vicky...I think your result set is incorrect...

What school do you go to?

Brett

8-)

I got it to work...

Corey

vmclear04
Starting Member

2 Posts

Posted - 2004-08-24 : 14:24:55

Hi Corey,

Thx for writting such a detailed message.
That looks pretty good. However, in my case, the information in the table is changing every hour. Fx: Student 1 might be gone in the next hour. And there is a new student registered. Or the status might change from Processing to Working and vice versus and so on. Again, I appreciate your help very much.

quote:

Originally posted by Seventhnight

does this help??


Declare @myTable table (Id varchar(100), OrderNum int, Status varchar(100))

Insert Into @myTable Values('Student1', 100, 'Completed')
Insert Into @myTable Values('Student1', 200, 'Processing')
Insert Into @myTable Values('Student1', 300, 'Ignore')
Insert Into @myTable Values('Student2', 100, 'Completed')
Insert Into @myTable Values('Student2', 400, 'Working')
Insert Into @myTable Values('Student2', 200, 'NotAvailable')
Insert Into @myTable Values('Student3', 600, 'Processing')
Insert Into @myTable Values('Student3', 200, 'Working')
Insert Into @myTable Values('Student3', 300, 'Working')
Insert Into @myTable Values('Student4', 400, 'Completed')
Insert Into @myTable Values('Student4', 200, 'Cancelled')
Insert Into @myTable Values('Student4', 300, 'Skip')


Select 
	id,
	OrderNum = case 
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working')) in (1,2) then
				(Select top 1 OrderNum From @myTable where id = A.id and Status in ('Processing','Working') Order By OrderNum)
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working'))=0 then
				(Select top 1 OrderNum From @myTable where id = A.id Order By OrderNum Desc)
			End,
	Status = case 
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working')) in (1,2) then
				(Select top 1 Status From @myTable where id = A.id and Status in ('Processing','Working') Order By OrderNum)
			when (Select count(Distinct Status) From @myTable where id = A.id and Status in ('Processing','Working'))=0 then
				(Select top 1 Status From @myTable where id = A.id Order By OrderNum Desc)
			End
From (Select Distinct id From @myTable) as A

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-24 : 16:37:04

So what is your new question?? This query is based entirely on the source table (in my case @myTable). You can supplement any table you have a mind to...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources