sqp procedre help

[shydev]

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 …

Thankyou

 

[Qik3Coder]

What are you having trouble with?

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[shydev]

I am having trouble with how to write this in SQL - in a stored procedure .

ALso Output table should look like this :


Output Table should produce this :

Request ID Status
001 C
002 NULL
003 C
004 NULL
Please advise …

 

[Qik3Coder]

faq183-694


A Sql statement is comprised of several main sections

SELECT {STUFF} FROM {TABLES} WHERE {CONDITIONS}

Your basic example starts to translate to:
SELECT {STUFF} FROM dbo.YourTableWhichYouDidNotGiveTheNameOf

SELECT RequestID, BookingID, Status
FROM dbo.YourTableWhichYouDidNotGiveTheNameOf

ahh screw it. Either you're going to figure this out or you're not.

Select DISTINCT RequestID, [Status] 
FROM @Table WHERE STATUS = 'C'
UNION ALL
SELECT DISTINCT RequestID, [Status] 
FROM @Table 
WHERE 
	RequestID not in(
		SELECT REQUESTID FROM @Table WHERE [Status]='C')
ORDER BY RequestID

Lodlaiden





[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]