DROP VIEW IF EXISTS AKUZ.V_RSOSZ_PND_OBJECT;
CREATE OR REPLACE VIEW AKUZ.V_RSOSZ_PND_OBJECT
AS
SELECT
    gen_random_uuid() 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
    LEFT JOIN akuz.t_medical_record mr2 ON mde.medicalrecordid = mr2.medicalrecordid
    JOIN akuz.t_rsosz_type_mkb_link m ON mdi.currentmkb = m.mkbcode
    WHERE m.type = 1
     ) mkb
WHERE
    mkb.rn = 1;