sql procedure - create output table

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

General SQL Server Forums

New to SQL Server Programming

sql procedure - create output table

Author

Topic

ashwika
Starting Member

9 Posts

Posted - 2011-07-19 : 17:50:54

I have a table which contains many records for a RequestID ,
Each request has a BookingID .

Aim : to write a stored procedure and create an output table
which contains a RequestID and Status ( If any request has a Status of ‘C’ then Status
overrides any other status other status is whatever is on input.

Input Table

Request ID Booking ID Status
001 50 NC
001 51 C
001 52 NC
001 53 C
002 54 NC
002 55 NC
002 56 NC
003 57 C
004 58 NC
004 59 NC
004 60 NC

Output Table should produce this :

Request ID Status
001 C
002 NC
003 C
004 NC

Please advise …

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 18:00:38

Are C and NC the only possible status? If so...

SELECT	RequestID, MIN(Status) status
FROM	yourTable
GROUP BY
	RequestID

ashwika
Starting Member

9 Posts

Posted - 2011-07-19 : 18:14:12

isnt MIN for finding the smallest value ???(must be an integer)

Status values can be NC, C, IA, or A

thanks

ashwika
Starting Member

9 Posts

Posted - 2011-07-19 : 18:39:59

BUt it should only output 'C' if present in any of the requests
otherwise Null
sorry output table should be :

Output Table should produce this :

Request ID Status
001 C
002 NULL
003 C
004 NULL

Please advise …
out

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-19 : 22:17:45

Ahaa, chenging the requirements on us huh? lol.

this will do it


select	requestid, min(case when status = 'C' then 'C' else null end) as status
from	yourtable
group by
	requestid

And no, it doesn't need to be an integer. Try it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources