Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a CASE login within a CASE logic Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:03 AM
Points: 177, Visits: 417
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.
Post #1498375
Posted Wednesday, September 25, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,441, Visits: 12,303
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1498393
Posted Wednesday, September 25, 2013 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:03 AM
Points: 177, Visits: 417
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.
Post #1498407
Posted Wednesday, September 25, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,441, Visits: 12,303
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1498412
Posted Wednesday, September 25, 2013 9:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:03 AM
Points: 177, Visits: 417
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.
Post #1498441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse