Hello i've been working on this query that i when a particular derived column reaches a certain number it adds 1 for every one over. For example the threshold for History is 4 but if the sum calculates 6 then the two extra get carried over to another column in this case PREP. Here is what i have so far. Thanks

SELECT

SUM(HISTORY) AS HISTORY,

SUM(ELA) AS ELA,

SUM(MATH) AS MATH,

SUM(SCIENCE) AS SCIENCE,

SUM(FL) AS FL,

SUM(VA) AS VA,

SUM (CASE WHEN HISTORY > 4 THEN 1

WHEN SCIENCE > 4 THEN 1

WHEN SCIENCE > 5 THEN 1

WHEN SCIENCE > 6 THEN 1

WHEN SCIENCE > 7 THEN 1

WHEN SCIENCE > 8 THEN 1

WHEN FL > 4 THEN 1 END ) as PREP

FROM (SELECT

SUM(CASE

WHEN c.U1 = 'A' THEN 1

ELSE 0

END) AS HISTORY,

SUM(CASE

WHEN c.U1 = 'B' THEN 1

ELSE 0

END) AS ELA,

SUM(CASE

WHEN c.U1 = 'C' THEN 1

ELSE 0

END) AS MATH,

SUM(CASE

WHEN c.U1 = 'D' THEN 1

ELSE 0

END) AS SCIENCE,

SUM(CASE

WHEN c.U1 = 'E' THEN 1

ELSE 0

END) AS FL,

SUM(CASE

WHEN c.U1 = 'F' THEN 1

ELSE 0

END) AS VA,

SUM(CASE

WHEN c.U1 = 'G' THEN 1

ELSE 0

END) AS Prep

FROM CRS AS c

INNER JOIN HIS AS h

ON h.CN = c.CN

AND h.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC')

AND h.MK NOT LIKE '%N%'

INNER JOIN STU AS s

ON s.ID = h.PID

AND s.ID = @ID

AND s.TG NOT IN ('N', '*', 'I')

AND s.SC = @SC

WHERE (c.U1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G'))