question

RonTech1 avatar image
0 Votes"
RonTech1 asked GuoxiongYuan-7218 answered

Case statement within a select

Hi,

I have a sample table of machines like the below

78770-table.png



CREATE TABLE [dbo].[apps](
[Num] [float] NULL,
[Counts] [float] NULL,
[Links] [float] NULL,
[Result] [float] NULL,
[Code] [nvarchar](255) NULL
) 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

sql-server-transact-sql
table.png (5.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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];
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.