Robert klimes (7/30/2015)
I'd like to thank everyone for their contributions.Even if supplied solution did not give desired results, they did give me different ways of looking at problems and , in the end, that is just as helpful because i will be able to use those strategies in future problems.
Thanks all :w00t:
I finally have something that works. Not necessarily elegant and I have no idea what it will perform like, but the results are what is expected.
Here's the code:
CREATE TABLE #gpa (
[student] int,
[term] int,
[course] varchar(10),
[mark] decimal(5,4)
);
INSERT INTO #gpa (student, term, course, mark)
VALUES (1,1,'math',3),
(1,1,'eng',4),
(1,1,'hist',4.5),
(1,1,'anth',4.5),
(1,2,'comp',3),
(1,2,'math',4),
(1,2,'bio',4.5),
(1,2,'chem',4.5),
(1,3,'soc',4.5),
(1,3,'math',4.5),
(1,3,'bio',4),
(1,3,'chem',4);
SELECT *
FROM #gpa
ORDER BY student, term, course;
WITH MAX_MARKS AS (
SELECT DISTINCT G.student, G.course, G.term,
MAX(G.mark) OVER(PARTITION BY G.student, G.course ORDER BY G.term) AS MAX_MARK
FROM #gpa AS G
),
MARKS_COUNT AS (
SELECT G.student, G.term, G.course,
CASE WHEN G.term = 1 THEN 1 ELSE 0 END AS VALID1,
CASE
WHEN G.term = 1 THEN
CASE
WHEN NOT EXISTS (
SELECT 1
FROM gpa AS G2
WHERE G2.student = G.student
AND G2.course = G.course
AND G2.term = G.term + 1
AND G2.mark >= G.mark) THEN 1
ELSE 0
END
WHEN G.term = 2 THEN
CASE
WHEN NOT EXISTS (
SELECT 1
FROM gpa AS G1
WHERE G1.student = G.student
AND G1.course = G.course
AND G1.term = G.term - 1
AND G1.mark >= G.mark) THEN 1
ELSE 0
END
WHEN G.term = 3 THEN 0
END AS VALID2,
CASE
WHEN G.term < 3 THEN
CASE
WHEN NOT EXISTS (
SELECT 1
FROM gpa AS GA
WHERE GA.student = G.student
AND GA.course = G.course
AND GA.term <> G.term
AND GA.mark > G.mark) THEN 1
ELSE 0
END
ELSE
CASE
WHEN NOT EXISTS (
SELECT 1
FROM gpa AS G12
WHERE G12.student = G.student
AND G12.course = G.course
AND G12.term < G.term
AND G12.mark >= G.mark) THEN 1
ELSE 0
END
END AS VALID3
FROM #gpa AS G
)
SELECT DISTINCT M.student, M.term,
CASE M.term
WHEN 1 THEN SUM(M.VALID1 * G.MAX_MARK) OVER (PARTITION BY M.student ORDER BY M.term)
WHEN 2 THEN SUM(M.VALID2 * G.MAX_MARK) OVER (PARTITION BY M.student ORDER BY M.term)
WHEN 3 THEN SUM(M.VALID3 * G.MAX_MARK) OVER (PARTITION BY M.student ORDER BY M.term)
END /
CASE M.term
WHEN 1 THEN SUM(M.VALID1) OVER(PARTITION BY M.student)
WHEN 2 THEN SUM(M.VALID2) OVER(PARTITION BY M.student)
WHEN 3 THEN SUM(M.VALID3) OVER(PARTITION BY M.student)
END AS AVERAGE_GPA
FROM MARKS_COUNT AS M
INNER JOIN MAX_MARKS AS G
ON M.student = G.student
AND M.term = G.term
AND M.course = G.course
DROP TABLE #gpa
Here's the results:
studenttermAVERAGE_GPA
114
124.142857
134.25
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)