TSQL Conditional Case

Question

Good day,

I have the below table for student marks

CREATE TABLE [dbo].[students](
[Month] nvarchar NULL,
[Student_No] [float] NULL,
[Mark] [float] NULL,
[Code] nvarchar NULL,
[Subject] nvarchar NULL
) ON [PRIMARY]
GO
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'May', 570, 25.33, N'B', N'Biology')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'June', 730, 34.02, N'B', N'Biology')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'July', 381, 29.81, N'B', N'Biology')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'August', 580, 20.27, N'B', N'Biology')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'September', 996, 42.59, N'B', N'Biology')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'October', 730, 19.79, N'Q', N'Programming')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'November', 381, 34.97, N'Q', N'Programming')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'December', 580, 100, N'M', N'English')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'January', 996, 96.67, N'M', N'English')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'February', 570, 93.33, N'M', N'English')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'March', 730, 93.75, N'M', N'English')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'April', 381, 96.92, N'M', N'English')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'May', 580, 0, N'C', N'Physics')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'June', 996, 100, N'C', N'Physics')
INSERT [dbo].[students] ([Month], [Student_No], [Mark], [Code], [Subject]) VALUES (N'July', 730, 50, N'C', N'Physics')
GO

The goal is to compute a final score for each student. The final score is made up of percentage contributions from each subject mark:
Biology = 10% contribution
Programming = 25% contribution
English = 35% contribution
Physics = 30% contribution

Rules to be applied for each student final score are the below:

• if there is a mark for all subjects, final score is sum of equal contribution of all subjects / 100
• if no Programming mark or 0%, final score is sum of equal contribution of all subjects / 75
• if no Physics mark or 0%, final score is sum of equal contribution of 3 subjects with marks + 30% (thus full physics contribution) / 100
• students will always have a mark for Biology and English
• students can be missing either Programming or Physics, or both Please advise, and or assist. Many thanks, Regards

Answer 1

Good day,

I think that I understood what you need but not sure. please check if this fit your needs and if not then please post the expected result according to the sample data and if needed then add some more rows to the sample.

;WITH MyCTE as (
    SELECT [Student_No], [Biology], [English], [Programming], 
        [Physics] = CASE WHEN ISNULL([Physics],0)=0 THEN 100 ELSE [Physics] END
    FROM (
        SELECT [Student_No], [Mark], [Subject]  
        FROM [dbo].[students]
    ) p  
    PIVOT(  
        MAX (MARK)  
        FOR [Subject] IN  ( [Biology], [Programming], [English], [Physics] )  
    ) AS pvt
)
SELECT [Student_No],
    TotalMark = CASE
        WHEN [Programming] IS NOT NULL AND [Physics] IS NOT NULL
            THEN (([Biology] * 10) + ([Programming] * 25) + ([English] * 35) + ([Physics] * 30))/100
        WHEN ISNULL([Programming],0) = 0
            THEN (([Biology] * 10) + ([English] * 35) + ([Physics] * 30))/75
        ELSE 9999
    END
FROM MyCTE