How to use Case Statement in Query.

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

How to use Case Statement in Query.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-11-12 : 05:42:50

Dear All,

I am having following query and output, in that i want to use case condition and hardcode the value means suppose Rate_Name is 'Interstate' then '5' else '7'. 5 and 7 are Rate type should come new column as following output,

Desired Output:

Version Rate_Name Rate_Type
25 Interstate 5
25 Intrastate 7

Query:
SELECT DT.Version,CT.Name As Rate_NameFROM tb_lcr_rate_deck_detail DT
LEFT OUTER JOIN tb_lcr_rate_deck RD ON RD.ID = DT.Rate_Deck_ID
LEFT OUTER JOIN tb_lcr_rate_deck_financial RDF ON RDF.Rate_Deck_Details_ID = DT.ID
LEFT OUTER JOIN tb_lcr_call_type CT ON CT.ID = RDF.Call_type_ID
WHERE RD.Customer_ID = '1917D5FF-0C71-4095-8690-0BA43E0390E0' and RD.ID= '295CCE1F-8313-4263-8BAF-DE0E1027D633'and DT.VERSION = '25' and CT.Name != 'International'

Output :

Version Rate_Name
25 Interstate
25 Intrastate

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-12 : 06:23:19

Are you looking for this ?

SELECT DT.Version,CT.Name As Rate_Name,
Case when CT.Name ='Interstate' Then 5 Else 7 End as Rate_Type
FROM tb_lcr_rate_deck_detail DT
LEFT OUTER JOIN tb_lcr_rate_deck RD ON RD.ID = DT.Rate_Deck_ID
LEFT OUTER JOIN tb_lcr_rate_deck_financial RDF ON RDF.Rate_Deck_Details_ID = DT.ID
LEFT OUTER JOIN tb_lcr_call_type CT ON CT.ID = RDF.Call_type_ID
WHERE RD.Customer_ID = '1917D5FF-0C71-4095-8690-0BA43E0390E0' and RD.ID= '295CCE1F-8313-4263-8BAF-DE0E1027D633'and DT.VERSION = '25' and CT.Name != 'International'

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 06:23:31

select case Rate_Name when 'Interstate' then 5 else 7 end Newcolumn...Rest of your query.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 06:24:07

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-12 : 11:42:16

rather than hardcoding its better if you can maintain a type table with values 5,7 etc corresponding to rate name values so that you dont need to change the code whenever a new rate name comes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources