Need help with a sub-query

Question

Need help with a sub-query

n.heyen

SSCrazy

Points: 2849
More actions
April 2, 2009 at 8:52 am

#203140

I have a SQL 2000 database used for reporting. The data is extracted nightly from another database. The report writers have requested another table to 'flatten' a table to return a Y or N depending on the condition code.
An example:
ID LINE COND_CODE
123 1 10
123 2 A3
123 3 61
345 1 18
456 1 61
789 1 10
789 2 18
789 3 A3
What I need is this:
ID OUTLIER
123 Y
345 N
456 Y
789 N
The logic behind the request is that if there are at least one COND_CODE = 61 for an ID, flag as Y else flag as N.
I suspect I will need to insert this into a table and set up a job to do this after the data has been updated after the nightly load but if there are better ways to do this, I'm open for suggestions.
Sorry to ask such a simple question but I just don't get what I need to do to load this new table.
Thanks for any advice,
Norman

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

First of all for all your future posts, read this article http://qa.sqlservercentral.com/articles/T-SQL/61539/[/url]. This makes us save a lot of time generating table and insert scripts.

IF ( OBJECT_ID( 'tempdb..#SomeTable' ) IS NOT NULL )

DROP TABLE #SomeTable

CREATE TABLE #SomeTable

(

[ID] INT NOT NULL,

LINE TINYINT NOT NULL,

COND_CODE VARCHAR(2) NOT NULL

)

INSERT #SomeTable( ID, LINE, COND_CODE )

SELECT 123, 1, '10'

UNION ALL

SELECT 123, 2, 'A3'

UNION ALL

SELECT 123, 3, '61'

UNION ALL

SELECT 345, 1, '18'

UNION ALL

SELECT 456, 1, '61'

UNION ALL

SELECT 789, 1, '10'

UNION ALL

SELECT 789, 2, '18'

UNION ALL

SELECT 789, 3, 'A3'

SELECT [ID], MAX( CASE WHEN COND_CODE = '61' THEN 'Y' ELSE 'N' END ) AS OUTLIER

FROM #SomeTable

GROUP BY [ID]

--Ramesh

n.heyen SSCrazy Points: 2849 More actions · Answer 2

Thank you Ramesh, this looks like it will work just like I needed. And a thank you for pointing me to the correct way to post. I apologize for taking up your time.

Norman

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

You are welcome.., no problem this time but make sure it doesn't happen next time

--Ramesh