CODEWITHSUNDEEP
sqlsql-servercase
John S
John S

Reputation: 33

Update Multiple Columns using single case statement

I have written sql that creates 4 columns: Tier0, Tier1, Tier2 and Weight.

Weight is associated with the tiers such that

when Tier0 = 'X' then weight = 0,
when Tier1 = 'X' then weight = 0.7, and
when Tier2 = 'X' then weight = 1.

Right now my code for this portion of my SELECT statement is 

CASE 
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) > (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 'X' Else '' END AS Tier0,

CASE 
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) = (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 'X' Else '' END AS Tier1,

CASE 
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) < (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 'X' Else '' END AS Tier2,

CASE 
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) > (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 0
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) = (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 0.7
WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) < (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
THEN 1  
END AS Weight

Is there a way to contain that final long case staement in the first three case statements?

I would like to have one case statement for each Tier if possible, that both puts an X in the correct tier column, and also updates the weight column correctly.

Any help would be appreciated.

Upvotes: 0

Views: 673

Answers (3)

sstan
sstan

Reputation: 36543

You could calculate the Tier0, Tier1, Tier2 columns in a CTE, and then use that to calculate the Weight column:

with cte as (
  select 
    CASE 
    WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) > (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
    THEN 'X' Else '' END AS Tier0,

    CASE 
    WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) = (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
    THEN 'X' Else '' END AS Tier1,

    CASE 
    WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) < (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
    THEN 'X' Else '' END AS Tier2

  FROM ...)
SELECT Tier0, Tier1, Tier2,
       case when Tier0 = 'X' then 0
            when Tier1 = 'X' then 0.7
            when Tier2 = 'X' then 1
        end as weight
  from cte

At least this helps with the duplicating of the logic.

Upvotes: 0

Lamak
Lamak

Reputation: 70678

You could use a derived table:

SELECT  Tier0,
        Tier1,
        Tier2,
        CASE 
            WHEN Tier0 = 'X' THEN 0
            WHEN Tier1 = 'X' THEN 0.7
            WHEN Tier2 = 'X' THEN 1
        END [Weight]
FROM (Your current query here) AS T;

Or a CTE:

;WITH CTE AS
(
    Your current query here
)
SELECT  Tier0,
        Tier1,
        Tier2,
        CASE 
            WHEN Tier0 = 'X' THEN 0
            WHEN Tier1 = 'X' THEN 0.7
            WHEN Tier2 = 'X' THEN 1
        END [Weight]
FROM CTE;

Upvotes: 4

Roman Marusyk
Roman Marusyk

Reputation: 24619

What about this way?

CASE 
  WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) > (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
  THEN 0
  WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) = (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
  THEN 0.7 
  WHEN (SUM(vst.len_of_stay) / COUNT(vst.len_of_stay)) < (SUM(drg.drg_std_days_stay) / COUNT(drg.drg_std_days_stay))
  THEN 1  
END AS Weight

Upvotes: 1

Related Questions