How to work with case statement names

Question

Post reply

How to work with case statement names

itmasterw 60042

SSCrazy

Points: 2852

January 31, 2020 at 5:05 pm

#3722095

HI

In the following code, I have two fields, one comes from a case statement.

I need to make a computed field that subtracts one from the other (like this [Gross_Revenue])- [Debits_and_Credits_Cost].

But of course you cannot do this.

So what can I do?

Thank you

SELECT Count(*) AS 'Count of Tranactions',
              bh.[company_name]	   
			  ,[standard_entry_class_code]   
			  ,CASE 
			  WHEN  bh.[company_name] = 'Cash4WhateverTX' THEN Count(bh.[company_name]) * .25
			  WHEN bh.[company_name] = 'Cash4WhateverMO' THEN Count(bh.[company_name]) * .25 
			  WHEN bh.[company_name] = 'Cash4WhateverUT' THEN Count(bh.[company_name])  * .25        
			  WHEN bh.[company_name] = 'Cash4WhateverWI' THEN Count(bh.[company_name])  * .25
			  WHEN bh.[company_name] = 'Cash4WhateverIL' THEN Count(bh.[company_name])  * .25       
			  WHEN bh.[company_name] = 'CashLaneTX' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'CashLaneMO' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'CashLaneUT' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'LendYouCashTX' THEN Count(bh.[company_name])  * .50
			  WHEN bh.[company_name] = 'MYPD' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'PowerLend' THEN Count(bh.[company_name])  * .50
			  WHEN bh.[company_name] = 'DirectCashTX' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'DirectCashMO' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'DirectCashUT' THEN Count(bh.[company_name])  * .40
			  END AS [Gross_Revenue],
			  Count(bh.[company_name]) * .11 AS [Debits_and_Credits_Cost],
[Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 1

Put it in a common table expression and subtract it in a select from the CTE.

;WITH CTE AS
(
SELECT Count(*) AS 'Count of Tranactions',
              bh.[company_name]	   
			  ,[standard_entry_class_code]   
			  ,CASE 
			  WHEN  bh.[company_name] = 'Cash4WhateverTX' THEN Count(bh.[company_name]) * .25
			  WHEN bh.[company_name] = 'Cash4WhateverMO' THEN Count(bh.[company_name]) * .25 
			  WHEN bh.[company_name] = 'Cash4WhateverUT' THEN Count(bh.[company_name])  * .25        
			  WHEN bh.[company_name] = 'Cash4WhateverWI' THEN Count(bh.[company_name])  * .25
			  WHEN bh.[company_name] = 'Cash4WhateverIL' THEN Count(bh.[company_name])  * .25       
			  WHEN bh.[company_name] = 'CashLaneTX' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'CashLaneMO' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'CashLaneUT' THEN Count(bh.[company_name])  * .25 
			  WHEN bh.[company_name] = 'LendYouCashTX' THEN Count(bh.[company_name])  * .50
			  WHEN bh.[company_name] = 'MYPD' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'PowerLend' THEN Count(bh.[company_name])  * .50
			  WHEN bh.[company_name] = 'DirectCashTX' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'DirectCashMO' THEN Count(bh.[company_name])  * .40 
			  WHEN bh.[company_name] = 'DirectCashUT' THEN Count(bh.[company_name])  * .40
			  END AS [Gross_Revenue],
			  Count(bh.[company_name]) * .11 AS [Debits_and_Credits_Cost],
...
)
SELECT [Count of Tranactions],
       [company_name],
       [standard_entry_class_code],
       [Gross_Revenue],
       [Debits_and_Credits_Cost],
       [Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev
  FROM CTE

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

Add an outer query, something like this:

SELECT *, [Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev
FROM (
    SELECT Count(*) AS 'Count of Tranactions',
                  bh.[company_name]	   
			      ,[standard_entry_class_code]   
			      ,Count(bh.[company_name]) *
                  CASE WHEN bh.[company_name] IN ('Cash4WhateverTX', 'Cash4WhateverMO',
                      'Cash4WhateverUT', 'Cash4WhateverWI', 'Cash4WhateverIL',
                      'CashLaneTX', 'CashLaneMO', 'CashLaneUT') THEN .25 
                       WHEN bh.[company_name] IN ('MYPD', 'DirectCashTX',
                      'DirectCashMO', 'DirectCashUT') THEN .40
                      WHEN bh.[company_name] IN ('LendYouCashTX', 'PowerLend') THEN .50
			      END AS [Gross_Revenue],
			      Count(bh.[company_name]) * .11 AS [Debits_and_Credits_Cost]
    FROM ...
    GROUP BY ...
) AS derived

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!