IF OBJECT_ID('AKUZ.V_RSOSZ_PND_OBJECT') IS NOT NULL
    DROP VIEW AKUZ.V_RSOSZ_PND_OBJECT;
GO
CREATE VIEW AKUZ.V_RSOSZ_PND_OBJECT
AS
SELECT NEWID()         AS rsoszpndobjectid,
       mkb.patient     AS patient,
       mkb.mkb         AS mkbcode,
       mkb.diagnosis   AS diagnosis,
       mkb.includedate AS includedate,
       mkb.excludedate AS excludedate
FROM (
    SELECT ac.Patient           AS patient,
           mdi.CurrentMkb       AS mkb,
           mdi.CurrentDiagnosis AS diagnosis,
           mr2.PostingDate      AS excludedate,
           mr.PostingDate       AS includedate,
           ROW_NUMBER()
               OVER (
                   PARTITION BY ac.patient, mdi.CurrentMkb
                   ORDER BY MR.PostingDate ASC
                   )            AS rn
    FROM AKUZ.T_AMBULANCE_CARD AC
    JOIN AKUZ.T_MEDICAL_RECORD mr ON mr.AmbulanceCard = AC.AmbulanceCardID
    JOIN AKUZ.T_MENTAL_DISPANSERISATION_INIT mdi ON mdi.MedicalRecordID = mr.MedicalRecordID
    LEFT JOIN AKUZ.T_MENTAL_DISPANSERISATION_END mde ON mde.MentalDispanserisationInit = mdi.MedicalRecordID
    JOIN AKUZ.T_MEDICAL_RECORD mr2 ON mde.MedicalRecordID = mr2.MedicalRecordID
    JOIN AKUZ.T_MKB m ON mdi.CurrentMkb = m.MCBCode
    WHERE m.RsoszType = 1
     ) mkb
WHERE
    mkb.rn = 1
GO
