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-02-13 : 07:01:17

Hey All

I need your lovely assistance again.

Aim – when Fee_Code = ‘42B’ and month_end_date =>2013-02-01 change the Fee_Code from “42B” to “42C”. Anything prior to 2013-02-01 the fee_code needs to remain the same

I can do this as a case statement(as seen below) but this creates a new column. How can i overwrite this logic in the fee_code column ?

My query is

SELECT
FDMSAccountNo,
Fee_Code,
month_end_date,
sum(Fact_Fee_History.Retail_amount) as 'PCI',
Case
when
fee_code = '42B' and (month_end_date >='2013-02-01') then '42C' end as Test
from Fact_Fee_History
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where fee_Code IN ('42B','42C')
and FDMSAccountNo = '878212363880'
group by FDMSAccountNo, Month_end_date,Fee_Code,[Description]
order by Month_end_date desc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-02-13 : 07:38:19

SELECT
FDMSAccountNo,
Case
when fee_code = '42B' and month_end_date >='2013-02-01' then '42C'
else fee_code
end as Fee_Code,
month_end_date,
sum(Fact_Fee_History.Retail_amount) as 'PCI'
from Fact_Fee_History
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where fee_Code IN ('42B','42C')
and FDMSAccountNo = '878212363880'
group by FDMSAccountNo, Month_end_date,Fee_Code,[Description]
order by Month_end_date desc

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-13 : 07:44:37

Thanks webfred

I amended your logic to the following

SELECT
FDMSAccountNo,
month_end_date,
Case
when fee_code = '42B' and month_end_date >='2013-02-01' then '42C' else fee_code end as Fee_Code,
isnull(sum(case when fee_code ='42C' Then (Retail_amount) else 0 end),0) as [Repeat],
isnull(sum(case when fee_code ='42B' Then (Retail_amount) else 0 end),0) as [Non Repeat],
sum(Fact_Fee_History.Retail_amount) as 'PCI'
from Fact_Fee_History
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where fee_Code IN ('42B','42C')
and FDMSAccountNo = '878212363880'
group by FDMSAccountNo, Month_end_date,Fee_Code,[Description]
order by Month_end_date desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources