Query with case condition. - 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

Query with case condition.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 04:55:28

Dear All,

I am having following query and output, and i want desired output.
Can anybody tell me how to solve this one ?

Query:
SELECT NPA,NXX,ISNULL(Thousands_Group,'')as Thousands_Group,
CASE WHEN Call_type_ID ='INTERSTATE' THEN Rate
ELSE '0' END AS Interstate_Rate,
CASE WHEN Call_type_ID ='INTRASTATE' THEN Rate
ELSE '0' END AS Intrastate_Rate
FROM
(
SELECT NPA,NXX,Thousands_Group,Default_Rate AS Rate,RDF.Call_type_ID
FROM tb_lcr_rate_deck_created_rate CR
INNER JOIN tb_lcr_rate_deck_financial RDF ON RDF.Rate_Deck_Details_ID = CR.Rate_Deck_Details_ID
-- AND RDF.Call_type_ID=CR.Call_type_ID
WHERE RDF.Rate_Deck_Details_ID = 'DA757669-CAC5-4AB3-88DE-8ECB9E5E882C' AND RDF.Call_type_ID <> 'INTERNATIONAL'
) AS res
Output:

NPA NXX Thousands_Group Interstate_Rate Intrastate_Rate
---------- ---------- --------------- --------------- ---------------
440 941 0 0.0065
440 941 0.0023 0
216 438 0 0.0065
216 438 0.0023 0

Desired Output:

NPA NXX Thousands_Group Interstate_Rate Intrastate_Rate
---------- ---------- --------------- --------------- ---------------
440 941 0.0023 0.0065
216 438 0.0023 0.0065

Thanks and Regard's
Harish Patil

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-12 : 05:02:54

Select
Col1
,Col2
,Max(Col3)
,Max(Col4)
From tableName
Group by Col1,Col2

Hope this Helps!

Cheers!
MIK

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 05:24:04

Dear Friend,

Ok. But how to implement this one in my query coz i am using case condition. Can u plz tel me.
I am not able to do that.

Thanks and Regard's
Harish Patil

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 05:36:56

Rear Friend,

Thanks a lot. I tried and i solved that issue.

Thnks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-12 : 06:03:20

:) dear there is no need for the Case in order to get the desired output. you can simply get it though the query i have mentioned and obviously you will need to replace the column names with the exact names as in your table.

Also if you need to impose some conditions simply put them in the where clause or if they are applying over groups/data set include a Having Clause!

Cheers!
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 08:30:29

even you apply MAX over CASE expression in your query

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-12 : 08:37:42

quote:
Originally posted by visakh16

even you apply MAX over CASE expression in your query

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

Pardon but what do you mean by this!!!! did i mention something incorrect in my solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 08:47:17

sorry that was not for you. It was to OP.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources