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

General SQL Server Forums

New to SQL Server Programming

Case statement help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-23 : 04:53:47

Hey guys

I hope your well,
I am struggling creating a case statement for the following, and i am hoping you can help

Aim – Create a new column called “Type”. Type will be populated with the following “Agent, Corporate, Chain , Outlet”.

SELECT
[FDMSAccountNo]
,[Chain_Chain_No]
,[Corp_Chain_No]
,[Agent_Chain_No]
FROM [FDMS].[dbo].[Dim_Outlet]

[FDMSAccountNo] = Outlet
[Chain_Chain_No] = Chain
[Corp_Chain_No] = Corporate
[Agent_Chain_No] = Agent

Basically if FDMSAccountNo is not found in [Chain_Chain_No] ,[Corp_Chain_No],[Agent_Chain_No] then its an outlet

If FDMSAccountNo is found in [Chain_Chain_No]
But not[Corp_Chain_No]or [Agent_Chain_No] then its an Chain

If FDMSAccountNo is found in [Corp_Chain_No]
But not[Chain_Chain_No] or [Agent_Chain_No] then its an Coporate

If FDMSAccountNo is found in [Agent_Chain_No]
But not[Chain_Chain_No] or [Corp_Chain_No]then its an Agent

Looking forward to your response

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 05:01:37

[code]SELECT
[FDMSAccountNo]
,[Chain_Chain_No]
,[Corp_Chain_No]
,[Agent_Chain_No]
, CASE WHEN [FDMSAccountNo] = [Chain_Chain_No] THEN 'Chain'
WHEN [FDMSAccountNo] = [Corp_Chain_No] THEN 'Corporate'
WHEN [FDMSAccountNo] = [Agent_Chain_No] THEN 'Agent'
ELSE 'Outlet'
END AS [Type]
FROM [FDMS].[dbo].[Dim_Outlet][/code]
--
Chandu