CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
mano
mano

Reputation: 308

SQL query to get a calculated column based on few conditions on existing columns

I am working on a query to get a score which would be the column added to existing table and are only calculated if at least three of the five items(item1,item2,item3,item4,item5) have been completed (value is 0, 1 or 2) other wise score is set to missing and set to 99. Score = (sum of item value/number of valid completed items) x number of items.

The input give below in the image is from a derived table.

Input and expected output

How should I implement the query to get expected results? Any help would be really appreciated.

Upvotes: 1

Views: 1883

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT item1, item2, item3, item4, item5,
        CASE 
           WHEN t.n >= 3 THEN t.s/(t.n * 1.0)  
           ELSE 99
        END AS Score
FROM mytable
CROSS APPLY (
   SELECT SUM(CASE WHEN item IN (0, 1, 2) THEN item ELSE 0 END),
          COUNT(CASE WHEN item IN (0, 1, 2) THEN 1 END)
   FROM (VALUES (item1), (item2), (item3), (item4), (item5))  AS x (item) ) AS t(s, n)

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

I suppose what you are showing is table rows and item1 to item5 are the table's columns.

A very simple and easy-to-read way is to look at the items, count the ones with values 0, 1 and 2 and add them up. Then use this result to apply your formula:

select item1, item2, item3, item4, item5,
  case when count_items >= 3 then sum_items / count_items * 5 else 99 end
from
(
  select item1, item2, item3, item4, item5,
    case when item1 in (0,1,2) then item1 else 0 end +
    case when item2 in (0,1,2) then item2 else 0 end +
    case when item3 in (0,1,2) then item3 else 0 end +
    case when item4 in (0,1,2) then item4 else 0 end +
    case when item5 in (0,1,2) then item5 else 0 end as sum_items,
    case when item1 in (0,1,2) then 1 else 0 end +
    case when item2 in (0,1,2) then 1 else 0 end +
    case when item3 in (0,1,2) then 1 else 0 end +
    case when item4 in (0,1,2) then 1 else 0 end +
    case when item5 in (0,1,2) then 1 else 0 end as count_items
  from mytable
) summed;

Upvotes: 3

Serg
Serg

Reputation: 22811

A bit different usage of cross apply

with d as (
    -- sample data
    select * 
    from (
        values 
        (1,1,2,2,4),
        (9,1,9,9,4)

    ) t(item1,item2,item3,item4,item5)
)
-- query
select *, case when n >=3 then (s + .0)/n else 99 end score
from d
cross apply (
    select sum(itm) s, count(*) n from(
        select item1 itm where item1 between 0 and 2
        union all
        select item2 itm where item2 between 0 and 2
        union all
        select item3 itm where item3 between 0 and 2
        union all
        select item4 itm where item4 between 0 and 2
        union all
        select item5 itm where item5 between 0 and 2
        ) t2
    ) t

Upvotes: 2

Related Questions