pietlinden (8/18/2013)

---

Getting closer, I think.... really should have heeded my professor's advice and used a smaller dataset, but anyway! I think this is right

SELECT ProtocolNo

, WeekNumber

, Goal

-- ADD RTGoal here

, SUM(GOAL) OVER (PARTITION BY ProtocolNo

ORDER BY WeekNumber

ROWS UNBOUNDED PRECEDING) AS CumulativeGoal

, NewEnrolls

-- ADD RTEnrolls here

, SUM(NewEnrolls) OVER (PARTITION BY ProtocolNo ORDER BY WeekNumber) AS CumulativeEnrolls

, NewEnrolls - Goal AS OverUnder

FROM

(

SELECT ProtocolNo

, eg.WeekNumber

, Goal

, COUNT(e_PatientID) AS NewEnrolls

FROM EnrollmentGoal eg

LEFT JOIN Enroll e ON eg.ProtocolNo = e.e_ProtocolNo

AND e.PWeek = eg.WeekNumber

GROUP BY ProtocolNo, eg.WeekNumber, Goal

) x;