Case statement within a select

Question

Hi,

I have a sample table of machines like the below

CREATE TABLE [dbo].apps ON [PRIMARY]
GO
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 26, 18, 0.692307692307692, N'E')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 27, 22, 0.814814814814815, N'G')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 2, 2, 1, N'P')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (10, 1, 1, 1, N'E')
GO

A machine can have any different code.

I calculate my score using the code below, based on specified ratios ( 5% for code E machines, 95% for all other codes)

Select Num
,202012 as Yr_Month
,Sum(score) * 0.50 as score

From (

Select rs.Num
,Sum(Result) * 0.95 as score
,Sum(Links) as Link
From apps rs
Where rs.Code <> 'E'
Group By rs.Num

Union all

Select rs.Num
,Sum(Result) * 0.05 as score
,Sum(Links) as Link
From apps rs
Where Code = 'E'
Group By Num

) x
Group By Num

Result is the below:

Num Yr_Month score
3 202012 0.879344729344729
10 202012 0.025

My requirement is, if a machine ONLY has the E code (for example, in the table, machine Num = 10, only has code E), then the ratios will change - thus, the machine will have the complete 100% ratio

How can I update the above code to accommodate this requirement?

Desired result will be:

Num Yr_Month score
3 202012 0.879344729344729
10 202012 0.5

Please assist,

Thanks

Answer 1

Try something like this:

;
with F as
(
    select Num, sum(Links) links, 
        (select sum(Result) from apps where num = a.num and Code = 'E') as se,
        (select sum(Result) from apps where num = a.num and Code <> 'E') as sne
    from apps a
    group by Num
)
select Num, 202012 as Yr_Month, Links, case when se is not null and sne is null then se else isnull(se, 0) * 0.05 + isnull(sne, 0) * 0.95 end * 0.5 as Score
from F
order by Num

Answer 2

Try this:

SELECT x.[Num], '202012' AS Yr_Month, CASE WHEN y.[Code] = 'E' THEN 0.5 ELSE x.Score * 0.5 END AS Score
FROM (
    SELECT [Num], SUM(CASE WHEN [Code] = 'E' THEN [Result] * 0.05 ELSE [Result] * 0.95 END) AS Score
    FROM [dbo].[apps]
    GROUP BY [Num]
) AS x
LEFT JOIN (
    SELECT [Num], [Code]
    FROM [dbo].[apps]
    WHERE [Num] IN (SELECT [Num] FROM [dbo].[apps] WHERE [Code] = 'E')
    AND [Num] NOT IN (SELECT [Num] FROM [dbo].[apps] WHERE [Code] <> 'E')
) AS y ON y.[Num] = x.[Num];