How to work with case statement names

• itmasterw 60042

  SSCrazy

  Points: 2858

  More actions

  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
  
  

   

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  January 31, 2020 at 5:13 pm

  #3722098

  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: 101103

  More actions

  January 31, 2020 at 6:23 pm

  #3722107

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".