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

CREATE VIEW LD0VYCASEWORKERSERVICES AS
SELECT 
PERSON.PNR AS PERSONID, 
PERSON.NAMN AS NAME, 
SERVICES.FOMDAT AS FROMDATE, 
SERVICETYPE.KLARTEXT AS SERVICETYPE, 
HAND.NAMN AS CASEWORKER,
ORG.NAMN AS ORGANIZATION,  
MEDHAND.NAMN AS COCASEWORKER, 
ORGMEDH.NAMN AS ORGANIZATIONMEDH,
SERVICES.HAND,
SERVICES.MEDHAND,
SERVICES.TOMDAT AS STOPDAT,
SERVICES.INSNR AS SERVICEID
FROM LD0VYC20   SERVICES
LEFT JOIN LD0V0I HAND ON HAND.HAND = SERVICES.HAND
LEFT JOIN LD0V0I MEDHAND ON MEDHAND.HAND = SERVICES.MEDHAND
LEFT JOIN LD0VJ2I01 ORG ON ORG.HAND = SERVICES.HAND AND SERVICES.ORGID = ORG.ORGID AND SERVICES.ORGENHNR = ORG.ORGENHNR
LEFT JOIN LD0V0A PERSON ON PERSON.PNR = SERVICES.AKTPNR
LEFT JOIN LD0V0J01 ORGMEDH ON  ORGMEDH.ORGID = SERVICES.ORGID AND ORGMEDH.ORGENHNR = SERVICES.ORGENHNR2 
LEFT JOIN LD0V0Q01 SERVICETYPE ON SERVICETYPE.KOD = SERVICES.INSTYP AND SERVICETYPE.KODTYP = 'INSATS'
;
