If statement in combination with case statement

Question

If statement in combination with case statement

mic.con87

SSCommitted

Points: 1947
More actions
April 12, 2013 at 8:02 am

#292096

Hi,
I have 3 categories ie Voice, Data, SMS.
I have a list of case statements that I currently run based on these categories.
SUM(CASE WHEN categ = 'Voice'
AND rev <> 'ICT'
THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END
)/60 AS voice_tot_usg,
SUM(CASE WHEN categ = 'Data'
AND rev <> 'ICT'
THEN volume_kb ELSE 0 END
)/1024 AS data_tot_usg,
SUM(CASE WHEN categ = 'SMS'
AND rev <> 'ICT'
THEN count_events ELSE 0 END
)AS sms_tot_usg,
from table A
Problem is Voice has 20 case statements, Data has 15 and SMS has 10. For example
SUM(CASE WHEN categ = 'Voice'
AND rev = 'Roam'
THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END
)/60 AS voice_Roam_usg etc
What I would like to do is in my table specify the categ as a variable and then only have the relevant case statements run
so something like
If categ = 'Voice'
THEN
(Perform all 20 Voice case statements and ignore the Data and SMS ones)
The idea is that I have a massive fact table so if I can pass the Categ = voice in the where clause I can reduce the base and then insert the results of the voice case statements while ignoring the Data and SMS.
I will then pass Data in the where clause, which will reduce the fact table and then only run the data case statements etc.
Any ideas? I cant seem to get if statements to work in sql...
Thanks a million

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

IF statements control the flow of logic in a script or stored procedure. Case expressions are used to conditionally retrieve values based on conditions within a query.

I can't really tell what you are trying to do from your description.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Problem is Voice has 20 case statements,

Can you post up three or four of them please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

mic.con87 (4/12/2013)
Hi,
I have 3 categories ie Voice, Data, SMS.
I have a list of case statements that I currently run based on these categories.
SUM(CASE WHEN categ = 'Voice'
AND rev <> 'ICT'
THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END
)/60 AS voice_tot_usg,
SUM(CASE WHEN categ = 'Data'
AND rev <> 'ICT'
THEN volume_kb ELSE 0 END
)/1024 AS data_tot_usg,
SUM(CASE WHEN categ = 'SMS'
AND rev <> 'ICT'
THEN count_events ELSE 0 END
)AS sms_tot_usg,
from table A
Problem is Voice has 20 case statements, Data has 15 and SMS has 10. For example
SUM(CASE WHEN categ = 'Voice'
AND rev = 'Roam'
THEN Durtn_Seconds_Rtd_Evnts ELSE 0 END
)/60 AS voice_Roam_usg etc
What I would like to do is in my table specify the categ as a variable and then only have the relevant case statements run
so something like
If categ = 'Voice'
THEN
(Perform all 20 Voice case statements and ignore the Data and SMS ones)
The idea is that I have a massive fact table so if I can pass the Categ = voice in the where clause I can reduce the base and then insert the results of the voice case statements while ignoring the Data and SMS.
I will then pass Data in the where clause, which will reduce the fact table and then only run the data case statements etc.
Any ideas? I cant seem to get if statements to work in sql...
Thanks a million

If it were me, I'd likely have 3 different stored procedures or inline Table Valued Functions (1 for each category) and then a "controller" proc that executed on of those based on the selected category. You could also embed the 3 different SELECTs in a single proc that would be conditionally executed based on the category selection.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'll be honest, you will get much better answers if you could provide us with the more detail regarding your environment and what you are trying to accomplish. Please remember that we can't see what you see and without additional information all we can do is guess.

If you post the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (as a series of INSERT INTO statements) for the table(s), the expected results based on the sample data, and even the code you have already written we can provide much better answers and in return you will get tested code.