IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = 'LD0VYGallringsunderlag')
BEGIN
DROP VIEW LD0VYGallringsunderlag
END
GO
CREATE VIEW LD0VYGallringsunderlag
AS
SELECT B.AKTNR, B.TYPNR, C.AKTPNR, C01.PNR AS UTR_MEDSOK, C21.PNR AS INS_MEDSOK, C.AEVERKNR, C.STOPDAT, C.TMARK,  C.AEVERKTYP, C20.INSNR, C20.TOMDAT, (SELECT MAX(ADATUM) FROM LD0VYH WHERE AKTNR = B.AKTNR) AS AKTU_ADATUM
FROM LD0VYB B 
JOIN LD0VYA A ON A.PNR = B.AKTPNR 
JOIN LD0VYC C ON C.AKTNR = B.AKTNR  
LEFT OUTER JOIN LD0V0C01 C01 ON C.AEVERKNR = C01.AEVERKNR AND C01.MEDSOKANDE = 1 
LEFT OUTER JOIN LD0VYC20 C20 ON (C.AEVERKNR = C20.ARENDENR AND C.TMARK = 0) OR (C.AEVERKNR = C20.VERKNR AND C.TMARK = 1) 
LEFT OUTER JOIN LD0V0C21 C21 ON C21.INSNR = C20.INSNR AND C21.MEDSOKANDE = 1
GO
GRANT ALL ON LD0VYGallringsunderlag TO LD0ROANV
GO
