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 - 2014-03-18 : 12:18:25

Hi guys

I need some help,

I need to update my following case statement to say if [Call Id] => 1 then PCC, if there no CallID then do the case statement

My query is ...

Select
[Call Id],
CASE
When [Sub-Source] IN (
'Internet PPC','Offer-1for3month-Jan13','Offer-Switch-Jan13','Phone-Offer-1for3month-Jan13',
'Phone-PPC-Brand','Phone-PPC-Machines','Phone-PPC-Merchant','Phone-PPC-ProcPayments','Phone-PPC-Terminals',
'Post visit BingYahoo PPC','Post visit Google PPC','Post visit Referrer Referred','PPC-Brand',
'PPC-CreditCard','PPC-DCC','PPC-Ecommerce','PPC-Machines','PPC-Merchant',
'PPC-ProcPayments','PPC-Terminals','Switcher Cashback',
'PPC-remarketing') then 'PPC'

When [How did you hear of us] IN (
'Internet PPC','Offer-1for3month-Jan13','Offer-Switch-Jan13','Phone-Offer-1for3month-Jan13',
'Phone-PPC-Brand','Phone-PPC-Machines','Phone-PPC-Merchant','Phone-PPC-ProcPayments','Phone-PPC-Terminals',
'Post visit BingYahoo PPC','Post visit Google PPC','Post visit Referrer Referred','PPC-Brand',
'PPC-CreditCard','PPC-DCC','PPC-Ecommerce','PPC-Machines','PPC-Merchant',
'PPC-ProcPayments','PPC-Terminals','Switcher Cashback',
'PPC-remarketing') then 'PPC'

-- Identifying SE0 Channel
WHEN [Sub-Source] in (
'£1MonthFor3','At Work','ClientLine - Freefor3months','ConnectX-Online',
'Connectx-Print','Email','Google/Yahoo','HDC','Internet FDMS Website (SE0)','Internet Search Engine',
'Live Chat','New Customer','Partner Referral','Phone-Direct','Phone-Offer-RetraFd','Recommendation/Referral',
'Refer a Friend','RetraFd','SEO-1Monthsfor3','SEO-Switcher-Cashback','Phone-SEO') then 'SE0'

WHEN [How did you hear of us] in (
'£1MonthFor3','At Work','ClientLine - Freefor3months','ConnectX-Online',
'Connectx-Print','Email','Google/Yahoo','HDC','Internet FDMS Website (SE0)','Internet Search Engine',
'Live Chat','New Customer','Partner Referral','Phone-Direct','Phone-Offer-RetraFd','Recommendation/Referral',
'Refer a Friend','RetraFd','SEO-1Monthsfor3','SEO-Switcher-Cashback','Phone-SEO') then 'SE0'
ELSE 'Check' END as [Channel Indiciator]
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-18 : 13:01:37

Firstly, you should really create a table and join to it instead of putting all this in a case expression. I'm not realy clear what your logic should be, but here is a guess:

CASE 
	WHEN [Call Id] => 1 then 'PCC'
	ELSE
		-- Existing Case Expression
END

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-03-18 : 13:29:23

Hi Lamprey,

You are a *
the final outcome is
Case
WHEN [Call Id] >= '1' then 'PCC'
When [Sub-Source] IN (
'Internet PPC','Offer-1for3month-Jan13','Offer-Switch-Jan13','Phone-Offer-1for3month-Jan13',
'Phone-PPC-Brand','Phone-PPC-Machines','Phone-PPC-Merchant','Phone-PPC-ProcPayments','Phone-PPC-Terminals',
'Post visit BingYahoo PPC','Post visit Google PPC','Post visit Referrer Referred','PPC-Brand',
'PPC-CreditCard','PPC-DCC','PPC-Ecommerce','PPC-Machines','PPC-Merchant',
'PPC-ProcPayments','PPC-Terminals','Switcher Cashback',
'PPC-remarketing') then 'PPC'

When [How did you hear of us] IN (
'Internet PPC','Offer-1for3month-Jan13','Offer-Switch-Jan13','Phone-Offer-1for3month-Jan13',
'Phone-PPC-Brand','Phone-PPC-Machines','Phone-PPC-Merchant','Phone-PPC-ProcPayments','Phone-PPC-Terminals',
'Post visit BingYahoo PPC','Post visit Google PPC','Post visit Referrer Referred','PPC-Brand',
'PPC-CreditCard','PPC-DCC','PPC-Ecommerce','PPC-Machines','PPC-Merchant',
'PPC-ProcPayments','PPC-Terminals','Switcher Cashback',
'PPC-remarketing') then 'PPC'

-- Identifying SE0 Channel
WHEN [Sub-Source] in (
'£1MonthFor3','At Work','ClientLine - Freefor3months','ConnectX-Online',
'Connectx-Print','Email','Google/Yahoo','HDC','Internet FDMS Website (SE0)','Internet Search Engine',
'Live Chat','New Customer','Partner Referral','Phone-Direct','Phone-Offer-RetraFd','Recommendation/Referral',
'Refer a Friend','RetraFd','SEO-1Monthsfor3','SEO-Switcher-Cashback','Phone-SEO') then 'SE0'

WHEN [How did you hear of us] in (
'£1MonthFor3','At Work','ClientLine - Freefor3months','ConnectX-Online',
'Connectx-Print','Email','Google/Yahoo','HDC','Internet FDMS Website (SE0)','Internet Search Engine',
'Live Chat','New Customer','Partner Referral','Phone-Direct','Phone-Offer-RetraFd','Recommendation/Referral',
'Refer a Friend','RetraFd','SEO-1Monthsfor3','SEO-Switcher-Cashback','Phone-SEO') then 'SE0'
ELSE 'Check' END as [Channel Indiciator],

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources