Creating a CASE login within a CASE logic

Question

Creating a CASE login within a CASE logic

SteveEClarke

SSCertifiable

Points: 5161
More actions
September 25, 2013 at 7:57 am

#395549

I have the following code which seems to accumulate the complete file - rather than the upper record set -
When OperationPhase = 2 and ResCat = 'MC' - I want to sum it.
SUM( CASE OperationPhase WHEN 2 THEN
(CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpMC,
SUM( CASE OperationPhase WHEN 3 THEN
(CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalRunMC,
SUM( CASE OperationPhase WHEN 2 THEN
(CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpPERS,
SUM( CASE OperationPhase WHEN 3 THEN
(CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalRunPERS,
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

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

SteveEClarke (9/25/2013)
I have the following code which seems to accumulate the complete file - rather than the upper record set -
When OperationPhase = 2 and ResCat = 'MC' - I want to sum it.
SUM( CASE OperationPhase WHEN 2 THEN
(CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpMC,
SUM( CASE OperationPhase WHEN 3 THEN
(CASE ResCat when 'MC' then ResAllocTime else 0 end ) else 0 end ) as TotalRunMC,
SUM( CASE OperationPhase WHEN 2 THEN
(CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalSetUpPERS,
SUM( CASE OperationPhase WHEN 3 THEN
(CASE ResCat when 'PERS' then ResAllocTime else 0 end ) else 0 end ) as TotalRunPERS,

Need a little more detail here. What does upper record set mean? What exactly are you trying to do?

_______________________________________________________________

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/

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Sorry -

What I am trying to achieve is a CASE within a CASE I suppose

Two conditional checks ...

So ...

SUM ( CASE field_SEX WHEN "MALE" THEN

CASE field_PARENTS_ALIVE WHEN "YES" Then 1 ELSE 0 END) ELSE 0 END ) as MaleParents

SUM ( CASE field_SEX WHEN "FEMALE" THEN

CASE field_PARENTS_ALIVE WHEN "YES" Then 1 ELSE 0 END) ELSE 0 END ) as FeMaleParents

Does that make more sense ?

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

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

SteveEClarke (9/25/2013)
Sorry -
What I am trying to achieve is a CASE within a CASE I suppose
Two conditional checks ...
So ...
SUM ( CASE field_SEX WHEN "MALE" THEN
CASE field_PARENTS_ALIVE WHEN "YES" Then 1 ELSE 0 END) ELSE 0 END ) as MaleParents
SUM ( CASE field_SEX WHEN "FEMALE" THEN
CASE field_PARENTS_ALIVE WHEN "YES" Then 1 ELSE 0 END) ELSE 0 END ) as FeMaleParents
Does that make more sense ?

For two conditions just use AND as your check.

SUM(CASE WHEN field_SEX = 'MALE' AND field_PARENTS_ALIVE = 'YES' Then 1 ELSE 0 END) as MaleParents

SUM(CASE WHEN field_SEX = 'FEMALE' AND field_PARENTS_ALIVE = 'YES' Then 1 ELSE 0 END) as FeMaleParents

_______________________________________________________________

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/

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

Sorry - that seems to be working as expected -

Thanks -

I think when I tried that I had "CASE fieldname WHEN" rather than "CASE WHEN fieldname"

Regards

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.