help with query

Yeah, We need more information about the department and score tables.

Create a script that gives us demo data in table variables:

declare @scores table (caseID bigint, productCode bigint, score float)
insert into @scores (caseID, productCode, score)
values (45678, 205, 10),
       (45678, 205, 20),
           (45678, 205, 30),
           (45678, 205, 40)
declare @departments table (deptID bigint, deptName varchar(30), scoreMod int)
insert into @departments (deptID, deptName, scoreMod) 
values (1, 'Network', 2)
declare @case table (caseID bigint)
insert into @case (caseID)
values (45678),(12379)
declare @productCodes table (productCode bigint)
insert into @productCodes (productCode)
values (205),(206)

and we can help you.