CASE STATEMENT HELP - 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 2005 Forums

Transact-SQL (2005)

CASE STATEMENT HELP

Author

Topic

jimmyjazz1978
Starting Member

11 Posts

Posted - 2012-05-22 : 14:55:22

Table 1 - Deal ID, REF NOS, Type, Papa ID

Table 2 - Deal ID, Type
Making a column in a new view called Method used. The way the field is to be set is as follows ( 4 conditions);

If Deal ID from table 1 Exists in Table 2 and Type is not Null from Table 2.
Set Method used to be Y

If Deal ID does not exist in Table 1 and Type does not contain 27,42 or 55 in Table 1.
Set Method used to be Y

If Papa ID is null from Table 1, and Type does not contain 27,42 or 55 in Table 1.
Set Method used to be Y

Else

Set to N
Started it and thought wow!..

create view Master as (

select Deal ID, REF NOS, Type, Papa ID

[Method used]=
Case
When

from Table 1 A
)

sql

Bizzare

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-23 : 07:36:49

You probably won't even need this many case statements,
but the code is easy to read and will be easier to maintain


SELECT t1.DealID,t1.RefNos,t1.PapaID

,[MethodUsed] = CASE WHEN t2.dealid Is not null and t2.type is not null THEN 'Y'
                     WHEN t2.dealid is null and t2.type not in (27,42,55) THEN 'Y'
                     WHEN t1.papaid is null and t2.type not nn (27,42,55) THEN 'Y'
                     ELSE 'N'
                END
FROM table1  t1
LEFT JOIN table2 t2 on t1.dealid = t2.dealid

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources