CODEWITHSUNDEEP
sql
user3580480
user3580480

Reputation: 482

SQL sub query logic

I am trying to calculate values in a column called Peak, but I need to apply different calculations dependant on the 'ChargeCode'.

Below is kind of what I am trying to do, but it results in 3 columns called Peak - Which I know is what I asked for :)

Can anyone help with the correct syntax, so that I end up with one column called Peak?

Use Test

Select  Chargecode, 
    (SELECT 1 Where Chargecode='1') AS [Peak],
    (SELECT 1 Where Chargecode='1242') AS [Peak],
    Peak*2 AS [Peak],
    CallType 

from Daisy_March2014

Thanks

Upvotes: 0

Views: 92

Answers (3)

nathairtras
nathairtras

Reputation: 1

Since it depends on your charge code, I'm going to make a wild assumption that this might be an ongoing thing where new charge codes / rules could be added. Why not store this as metadata either in the charge code table or in a new table? You could generate the initial data with this: 

SELECT ChargeCode,
       Multiplier
INTO ChargeMeta
FROM (  
        Select 1 AS ChargeCode,
               1 AS Multiplier
        UNION ALL
        SELECT 1242 AS ChargeCode,
               1 AS Multiplier
        UNION ALL
        SELECT ChargeCode,
               2 AS Multiplier
        FROM Daisy_March2014
        WHERE ChargeCode NOT IN (1,1242) 
) SQ

Then just join to your original data. 

SELECT a.ChargeCode,
       a.Peak*b.Multiplier AS Peak
FROM Daisy_March2014 a
JOIN ChargeMeta b
    ON a.ChargeCode = b.ChargeCode

If you do not want to maintain all charge code multipliers, you could maintain your non-standard ones, and store the standard one in the SQL. This would be about the same as a case statement, but it may still add benefit to store the overrides in a table. At the very least, it makes it easier to re-use elsewhere. No need to check all the queries that deal with Peak values and make them consistent, if ChargeCode 42 needs to have a new multiplier set.

If you want to store the default in the table, you could use two joins instead of one, storing the default charge code under a value that will never be used. (-1?)

SELECT a.ChargeCode,
       a.Peak*COALESCE(b.Multiplier,c.Multiplier) AS Peak
FROM Daisy_March2014 a
LEFT JOIN ChargeMeta b ON a.ChargeCode = b.ChargeCode
LEFT JOIN ChargeMeta c ON c.ChargeCode = -1

Upvotes: 0

user3580480
user3580480

Reputation: 482

Thanks Gordon, I have marked you response as Answered. Here is the final working code:

(case when chargecode in ('1') then 1 when chargecode in ('1264') then 2 else Peak*2 end) as Peak,

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You want a case statement. I think this is what you are looking for:

Select  Chargecode, 
        (case when chargecode = '1'
              when chargecode = '1242' then 2
              else 2 * Peak
         end) as Peak,
        CallType
from Daisy_March2014;

Upvotes: 1

Related Questions