SQL sub query logic

Question

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

Gordon Linoff · Accepted Answer · 2014-04-30 16:56:38Z

1

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;

edited Apr 30, 2014 at 16:56

answered Apr 30, 2014 at 16:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3580480 Over a year ago

Thanks, that's almost it - but I need to be able to specify different values for different ChargeCodes: so something like: (case when chargecode in ('1') then 1 else (case when chargecode in ('1264') then 2 else Peak*2 end) as Peak, - But that doesn't work. How should I go about nesting case when? Thanks

user3580480 · Accepted Answer · 2014-04-30 17:03:10Z

0

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,

answered Apr 30, 2014 at 17:03

user3580480

4827 gold badges16 silver badges48 bronze badges

Comments

nathairtras · Accepted Answer · 2014-04-30 17:03:38Z

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

Collectives™ on Stack Overflow

SQL sub query logic

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related