CREATE OR ALTER VIEW LD0VAA0MOUSubGoal
AS SELECT
SUBGOAL.*,
SUBGOALTYPE.KLARTEXT AS SUBGOALTYPE_T,
PROF.NAMN AS RESPONSIBLE_T,
PROF2.NAMN AS SUBGOALRESP_T,
CASE WHEN FOLLOWUP.CREATEDATE IS NOT NULL THEN FOLLOWUP.CREATEDATE ELSE '00000000' END AS LATESTFOLLOWUP,
CASE WHEN ASSESSMENT.GRADE IS NOT NULL THEN ASSESSMENT.GRADE ELSE 0 END AS LATESTASSESSMENT,
CASE WHEN FOLLOWUP.NEXTDATE IS NOT NULL THEN (CASE WHEN FOLLOWUP.FINAL = 1 THEN '00000000' ELSE FOLLOWUP.NEXTDATE END) ELSE SUBGOAL.NEXTDATE END AS NEXTFOLLOWUP
FROM LD0V0MOUSubGoal SUBGOAL
OUTER APPLY (
    SELECT TOP 1 * FROM LD0P0MOUAssessment ASSESSMENT WHERE ASSESSMENT.FOLLOWUPID IN (SELECT FOLLOWUPID FROM LD0P0MOUFollowUp WHERE SUBGOALID = SUBGOAL.SUBGOALID)
    AND ASSESSMENT.GRADE > 0 ORDER BY ASSESSMENT.ASSESSMENTID DESC
) ASSESSMENT
LEFT OUTER JOIN LD0P0Q01 SUBGOALTYPE ON SUBGOALTYPE.KODTYP = 'MOUSUBGOAL' AND SUBGOALTYPE.KOD = SUBGOAL.SUBGOAL
LEFT OUTER JOIN LD0P0I PROF ON PROF.HAND = SUBGOAL.RESPONSIBLE
LEFT OUTER JOIN LD0P0I PROF2 ON PROF2.HAND = SUBGOAL.SUBGOALRESP
LEFT OUTER JOIN LD0P0MOUFollowUp FOLLOWUP ON ASSESSMENT.FOLLOWUPID IS NOT NULL AND FOLLOWUP.FOLLOWUPID = ASSESSMENT.FOLLOWUPID
GO

GRANT ALL ON LD0VAA0MOUSubGoal TO LD0ROANV
GO