IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0LMSimplePlaceParticipant')
BEGIN
    DROP VIEW LD0VAA0LMSimplePlaceParticipant
END
GO

CREATE VIEW LD0VAA0LMSimplePlaceParticipant AS 
WITH TagAgg AS (
    SELECT 
        PERSONNR,
        STRING_AGG(CAST(TAGGNAMN AS VARCHAR(20)), ', ') AS TAGGNAMN,
        STRING_AGG(CAST(SERIENR AS VARCHAR(20)), ', ') AS SERIENR
    FROM LD0VAA0CheckInOutTag
    GROUP BY PERSONNR
)
SELECT 
    U34.ENKELPLATSNR AS PLATSID,
    U10.INSKRIVNINGSNR,
    U30.PLANNR,
    U34.PLANAKTIVITETNR,
    U10.PERSONNR,
    A.KUNDNUMMER,
    A.NAMN,
    A.ADRESS,
    A.COADR,
    A.POSTNR,
    A.ORT,
    A.HEMTEL,
    A.ARBTEL,
    A.MOBIL,
    A.EPOSTHEM,
    Q1.KLARTEXT AS AKTIVTYP_TEXT,
    Q2.KLARTEXT AS AKTIVITET_TEXT,
    U34.PLANSTART,
    U34.PLANSLUT,
    U34.VERKSTART,
    U34.VERKSLUT,
    I.NAMN AS HANDLAGGARE,
    TagAgg.TAGGNAMN,
    TagAgg.SERIENR
FROM LD0VYU34 U34
INNER JOIN LD0VYU30 U30 ON U30.PLANNR = U34.PLANNR
INNER JOIN LD0VYU10 U10 ON U10.INSKRIVNINGSNR = U30.INSKRIVNINGSNR
INNER JOIN LD0V0A A ON A.PNR = U10.PERSONNR
LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'LNAKTIVTYP' AND Q1.KOD = U34.AKTIVTYP
LEFT JOIN LD0V0Q01 Q2 ON Q2.KODTYP = 'LNAKTIV' AND Q2.KOD = U34.AKTIVITET
LEFT JOIN LD0V0I I ON I.HAND = U34.HAND
LEFT JOIN TagAgg ON TagAgg.PERSONNR = U10.PERSONNR
WHERE U34.ENKELPLATSNR > 0
GO

GRANT ALL ON LD0VAA0LMSimplePlaceParticipant TO LD0ROANV
GO