Case statement help

Question

Post reply

Case statement help

charlie-337681

SSC Enthusiast

Points: 179
More actions
August 9, 2007 at 8:51 pm

#153737

Hi,
Im trying to output a column saying the kpi_desc of a sale depending on the conditions, however my case statement is wrong, Im not sure if this sort of conditioning is allowed by SQL, can someone please help or point me in another direction
SELECT Sum(dbo.tbl_kpi_sales.Value) AS SumOfValue, dbo.tblCampaign.idMacroSegment, dbo.tblCampaign.idCampaignType, dbo.tbl_kpi_sales.idKPI, dbo.tbl_kpi_sales.State,
kpi_desc = case dbo.when dbo.tblCampaign.idMacroSegment = 1 then
   case dbo.tbl_kpi_sales.idKPI
      when 2509 then 'Con Std'
      when 2513 then 'Con Std'
Else
      when 2500 then 'SME Std'
      when 2507 then 'SME Std'
end
elseif case dbo.when dbo.tblCampaign.idMacroSegment = 2 then
case dbo.tbl_kpi_sales.idKPI
      when 2509 then 'Con Acc'
      when 2513 then 'Con Acc'
Else
      when 2500 then 'SME Acc'
      when 2507 then 'SME Acc'
end
end
FROM ((dbo.tbl_kpi_sales INNER JOIN dbo.vw_SalesRepsByState_Unique ON dbo.tbl_kpi_sales.Consultant = dbo.vw_SalesRepsByState_Unique.UserID) INNER JOIN dbo.tblCampaign ON dbo.tbl_kpi_sales.idCampaign = dbo.tblCampaign.idCampaign) INNER JOIN dbo.tblCampaignType ON dbo.tblCampaign.idCampaignType = dbo.tblCampaignType.idCampaignType
WHERE (((dbo.tbl_kpi_sales.State) Is Not Null) AND ((dbo.tbl_kpi_sales.idKPI)=2500 Or (dbo.tbl_kpi_sales.idKPI)=2507 Or (dbo.tbl_kpi_sales.idKPI)=2509 Or (dbo.tbl_kpi_sales.idKPI)=2513 Or (dbo.tbl_kpi_sales.idKPI)=3007 Or (dbo.tbl_kpi_sales.idKPI)=3006 Or (dbo.tbl_kpi_sales.idKPI)=3004 Or (dbo.tbl_kpi_sales.idKPI)=3005))
GROUP BY dbo.tblCampaign.idMacroSegment, dbo.tblCampaign.idCampaignType, dbo.tbl_kpi_sales.idKPI, dbo.tbl_kpi_sales.State
ORDER BY dbo.tblCampaign.idMacroSegment

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sergiy SSC Guru Points: 110192 More actions · Answer 1

kpi_desc = case dbo.tblCampaign.idMacroSegment

when 1

then

case dbo.tbl_kpi_sales.idKPI

when 2509 then 'Con Std'

when 2513 then 'Con Std'

when 2500 then 'SME Std'

when 2507 then 'SME Std'

end

when 2

then

case dbo.tbl_kpi_sales.idKPI

when 2509 then 'Con Acc'

when 2513 then 'Con Acc'

when 2500 then 'SME Acc'

when 2507 then 'SME Acc'

end

END

_____________
Code for TallyGenerator

charlie-337681 SSC Enthusiast Points: 179 More actions · Answer 2

charlie-337681

SSC Enthusiast

Points: 179

August 9, 2007 at 11:01 pm

#725895

Thanks a heaps Sergiy

bledu SSChampion Points: 12335 More actions · Answer 3

i think you can use the OR statement.i.e.

when

2509 or 2513 then 'Con Std'

when

2500 or 2507 then 'SME Std'

Everything you can imagine is real.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Pretty sure "OR" can't be used here....

SELECT CASE RowNum

WHEN 1 OR 10 THEN '1 or 10'

ELSE 'Not 1 or 10'

END

FROM jbmTest

WHERE RowNum <=10

Server: Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'or'.

--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)
Intro to Tally Tables and Functions

bledu SSChampion Points: 12335 More actions · Answer 5

thank goodness it was a thought

Everything you can imagine is real.

Vladan SSC-Insane Points: 22072 More actions · Answer 6

No, not with this type of CASE statement. It could be done like this:

SELECT CASE

WHEN RowNum IN(1, 10) THEN '1 or 10'

ELSE 'Not 1 or 10'

END

FROM jbmTest

WHERE RowNum <=10

or, to use the CASE that was being asked about:

kpi_desc = CASE WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) THEN 'Con Std'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) THEN 'SME Std'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) THEN 'Con Acc'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) THEN 'SME Acc'

END

I have no idea whether there is some performance difference when compared with the statement Sergiy posted, but both statements would return the same result (unless I misread something..)

bledu SSChampion Points: 12335 More actions · Answer 7

charlie if possible can you please try Vladan's solution and see how it performs against the other solution by Sergiy. and of coz give us the results

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 8

Charlie, how many [idMacroSegment] do you use. is it only 1 and 2?

if so i am thinking that the [idMacroSegment] testing becomes redundant and the final description will be functionaly dependant on the KPI only

Everything you can imagine is real.

charlie-337681 SSC Enthusiast Points: 179 More actions · Answer 9

Thanks for the help guys, I did a performance test and to render my report the query below took 31 secs - the query below this one using the nested cases took abt 35 secs...so im sticking with the IN clause as it is also much neater...

kpi_desc = CASE WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) AND dbo.tblCampaign.idCampaignType IN (16,17,21,22,24,30,31) THEN 'SME Std'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) AND dbo.tblCampaign.idCampaignType IN (18,20,23) THEN 'SME Acc'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) AND dbo.tblCampaign.idCampaignType IN (16,17,21,22,24,30,31) THEN 'Con Std'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2509,2513) AND dbo.tblCampaign.idCampaignType IN (18,20,23) THEN 'Con Acc'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) AND dbo.tblCampaign.idCampaignType IN (16,17,18,21,22,24,30,31) THEN 'SME Std'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) AND dbo.tblCampaign.idCampaignType IN (20,23) THEN 'SME Acc'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) AND dbo.tblCampaign.idCampaignType IN (16,17,22,21,24,30,31) THEN 'Con Std'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (2500,2507) AND dbo.tblCampaign.idCampaignType IN (18,20,23) THEN 'Con Acc'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (3004) THEN 'Mob New'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (3004) THEN 'Mob New'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (3005) THEN 'Mob Ret'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (3005) THEN 'Mob Ret'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (3006) THEN 'Brd Band'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (3006) THEN 'Brd Band'

WHEN dbo.tblCampaign.idMacroSegment = 1 AND dbo.tbl_kpi_sales.idKPI IN (3007) THEN 'Pay TV'

WHEN dbo.tblCampaign.idMacroSegment = 2 AND dbo.tbl_kpi_sales.idKPI IN (3007) THEN 'Pay TV'

END

against this query

kpi_desc = case dbo.tbl_kpi_sales.idKPI

when 3004 then '11. Mob New'

when 3005 then '12. Mob Ret'

when 3006 then '9. Brd Band'

when 3007 then '10. Pay TV'

when 2509

then

case dbo.tblCampaign.idMacroSegment

when 1

then

Case dbo.tblCampaign.idCampaignType

when 18 then '6. SME Acc'

when 20 then '6. SME Acc'

when 23 then '4. SF Acc'

when 16 then '5. SME Std'

when 17 then '5. SME Std'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 24 then '5. SME Std'

when 30 then '5. SME Std'

when 31 then '5. SME Std'

end

when 2

then

Case dbo.tblCampaign.idCampaignType

when 18 then '2. Con Acc'

when 20 then '2. Con Acc'

when 23 then '4. SF Acc'

when 16 then '1. Con Std'

when 17 then '1. Con Std'

when 22 then '3. SF Std'

when 21 then '3. SF Std'

when 24 then '1. Con Std'

when 30 then '1. Con Std'

when 31 then '1. Con Std'

end

when 2513

then

case dbo.tblCampaign.idMacroSegment

when 1

then

Case dbo.tblCampaign.idCampaignType

when 18 then '6. SME Acc'

when 20 then '6. SME Acc'

when 23 then '4. SF Acc'

when 16 then '5. SME Std'

when 17 then '5. SME Std'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 24 then '5. SME Std'

when 30 then '5. SME Std'

when 31 then '5. SME Std'

end

when 2

then

Case dbo.tblCampaign.idCampaignType

when 18 then '2. Con Acc'

when 20 then '2. Con Acc'

when 23 then '4. SF Acc'

when 16 then '1. Con Std'

when 17 then '1. Con Std'

when 22 then '3. SF Std'

when 21 then '3. SF Std'

when 24 then '1. Con Std'

when 30 then '1. Con Std'

when 31 then '1. Con Std'

end

when 2500

then

case dbo.tblCampaign.idMacroSegment

when 1

then

Case dbo.tblCampaign.idCampaignType

when 16 then '5. SME Std'

when 17 then '5. SME Std'

when 18 then '5. SME Std'

when 20 then '6. SME Acc'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 23 then '4. SF Acc'

when 24 then '5. SME Std'

when 30 then '5. SME Std'

when 31 then '5. SME Std'

end

when 2

then

Case dbo.tblCampaign.idCampaignType

when 16 then '1. Con Std'

when 17 then '1. Con Std'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 24 then '1. Con Std'

when 30 then '1. Con Std'

when 31 then '1. Con Std'

when 20 then '2. Con Acc'

when 18 then '2. Con Acc'

when 23 then '4. SF Acc'

end

when 2507

then

case dbo.tblCampaign.idMacroSegment

when 1

then

Case dbo.tblCampaign.idCampaignType

when 16 then '5. SME Std'

when 17 then '5. SME Std'

when 18 then '5. SME Std'

when 20 then '6. SME Acc'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 23 then '4. SF Acc'

when 24 then '5. SME Std'

when 30 then '5. SME Std'

when 31 then '5. SME Std'

end

when 2

then

Case dbo.tblCampaign.idCampaignType

when 16 then '1. Con Std'

when 17 then '1. Con Std'

when 21 then '3. SF Std'

when 22 then '3. SF Std'

when 24 then '1. Con Std'

when 30 then '1. Con Std'

when 31 then '1. Con Std'

when 20 then '2. Con Acc'

when 18 then '2. Con Acc'

when 23 then '4. SF Acc'

end

Sergiy SSC Guru Points: 110192 More actions · Answer 10

If you would use 30% of effort you wasted on this script for building tables holding all those conditions you'd get clean, short script implementing manageable solution.

By "manageable" I mean a solution when users don't need you to change a rule, they can do it by themselves from UI by adding or removing records in look-up tables.

_____________
Code for TallyGenerator