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.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-21 : 05:27:08
|
Hey Guys Hope your wellI need some urgent help Aim – Create a case statement to say if “Arepeatable” is null, then use “Cont Band” Column. If “Arepeatable” is not null then use “ARepeatable”My query is--- Results --- SELECT [Lead ID],[MID Internal (Merchant ID)],[MID External (Merchant ID)]---Calculating Contribution Banding --Casewhen [Business Turnover] between '0' and '6999' then '60'when [Business Turnover] between '7000' and '14999' then '133' when [Business Turnover] between '15000' and '19999' then '193' when [Business Turnover] between '20000' and '29999' then '250' when [Business Turnover] between '30000' and '49999' then '260' when [Business Turnover] between '50000' and '99999' then '413' when [Business Turnover] between '100000' and '199999' then '675' when [Business Turnover] between '200000' and '299999' then '1000' when [Business Turnover] between '300000' and '499999' then '1400' when [Business Turnover] between '500000' and '999999' then '1875' when [Business Turnover] between '1000000' and '4999999' then '5000' when [Business Turnover] between '5000000'and '19999999' then '18750' when [Business Turnover] between '20000000' and '49999999' then '42000' when [Business Turnover] > '50000000' then '75000' else 'No Sales' end as [Cont Band] ,[ARepeatable]FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting] SMRleft join [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting_Financials] SMF on SMR.[MID Internal (Merchant ID)] = SMF.[AccountId] |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-21 : 05:45:24
|
use a CTE; with cte as( < your existing query>)select case when Arepeatable is null then [Cont Band] else [ARepeatable] endfrom cte KH[spoiler]Time is always against us[/spoiler] |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-21 : 06:07:10
|
HI KH So you the query is SELECT [Lead ID],[MID Internal (Merchant ID)],[MID External (Merchant ID)]---Calculating Contribution Banding -- ; with cte as( CaseWHEN [ARepeatable] is null then 'PPC' when [Business Turnover] between '0' and '6999' then '60'when [Business Turnover] between '7000' and '14999' then '133' when [Business Turnover] between '15000' and '19999' then '193' when [Business Turnover] between '20000' and '29999' then '250' when [Business Turnover] between '30000' and '49999' then '260' when [Business Turnover] between '50000' and '99999' then '413' when [Business Turnover] between '100000' and '199999' then '675' when [Business Turnover] between '200000' and '299999' then '1000' when [Business Turnover] between '300000' and '499999' then '1400' when [Business Turnover] between '500000' and '999999' then '1875' when [Business Turnover] between '1000000' and '4999999' then '5000' when [Business Turnover] between '5000000'and '19999999' then '18750' when [Business Turnover] between '20000000' and '49999999' then '42000' when [Business Turnover] > '50000000' then '75000' else 'No Sales' end as [Cont Band] )select case when Arepeatable is null then [Cont Band] else [ARepeatable] endfrom cteFROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting] SMRleft join [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting_Financials] SMF on SMR.[MID Internal (Merchant ID)] = SMF.[AccountId] |
|
|
|
|
|