    
CREATE OR ALTER FUNCTION GetModifiedAfterValuesForDecisionAPI(    
    @StartTime DATETIME2,    
    @EndTime DATETIME2  
)    
RETURNS TABLE    
AS    
RETURN    
(    
	
	WITH STAT3 AS (
	SELECT 1 AS KOD, 'SOL' AS KLARTEXT
	UNION
	SELECT 2 AS KOD, 'LVU' AS KLARTEXT
	UNION
	SELECT 3 AS KOD, 'LVM' AS KLARTEXT
	UNION
	SELECT 4 AS KOD, 'Föräldrabalken' AS KLARTEXT
	)
	SELECT DISTINCT
	E.BESLUTNR,      
	E.STARTTIME,      
	E.ENDTIME,      
	E.DATUM,      
	E.FOMDAT,      
	E.TOMDAT,      
	E.AEVERKNR,      
	E.INSNR,      
	E.BELOPP,      
	E.AKTUALNR,      
	E.BESFATT,      
	E.BESLUT,      
	E.BESLTYP,      
	E.ORSAK,      
	E.VERKSEJ,      
	E.GEMVARD,      
	E.PKLTYP,      
	E.BARNVAEG,      
	E.VARDNHAV,      
	E.UNDANTAGSTATISTIK,      
	E.ORSAKMED,      
	VI.NAMN BNAMN_T,                   
	VI.TJANST BTJANST_T,                   
	Q01.KLARTEXT BORSAK_T,                   
	Q01.XTXT1 BORSAKRUBRIK_T,                   
	Q01_2.KLARTEXT BESLUT_T,                   
	Q01_3.KLARTEXT BMEDORSAK_T,          
	Q01_4.KLARTEXT AS BESLTYP_T,      
	Q01_3.XTXT1 BMEDORSAKRUBRIK_T,                   
	Q01_2.XNUM5 AMOUNT_TO_BALANCE,                   
	Q01_2.XNUM2 LEADS_TO_SERVICE,                   
	Q01_2.XNUM33 LEADS_TO_ASSIGNMENT,      
	T10.KLARTEXT STATGRP1_T,      
	T11.KLARTEXT STATGRP2_T,      
	T12.KLARTEXT STATGRP3_T,      
	T13.KLARTEXT STATGRP4_T,            
	T14.KLARTEXT STATGRP5_T,      
	CASE 
	    WHEN E.AEVERKNR > 0 THEN CT.KUNDNUMMER
	    WHEN E.INSNR > 0 THEN CT20.KUNDNUMMER
	    WHEN E.AEVERKNR = 0 AND E.INSNR = 0 THEN H.KUNDNUMMER
	END AS KUNDNUMMER,
	CASE 
	    WHEN E.AEVERKNR > 0 THEN CT.PNR
	    WHEN E.INSNR > 0 THEN CT20.PNR
	    WHEN E.AEVERKNR = 0 AND E.INSNR = 0 THEN H.PNR
	END AS PNR,      
	INV.HAND AS INVESTIGATIONSCASEWORKERID,       
	SERV.HAND AS SERVICESCASEWORKERID,      
	AKPER.HAND AS EXECUTIONSCASEWORKERID,            
	PROF3.NAMN AS INVESTIGATIONSCASEWORKER,       
	PROF2.NAMN AS SERVICESCASEWORKER,      
	PROF1.NAMN AS EXECUTIONSCASEWORKER,      
	J01_SERV.NAMN AS SERVICESORG,      
	J01_INV.NAMN AS INVESTIGATIONSORG,      
	J01_AKPER.NAMN AS EXECUTIONSORG,      
	L20.ORGID AS SERVICESORGID,      
	L20.ORGENHNR AS SERVICESORGUNITID,        
	INV.ORGID AS INVESTIGATIONSORGID,      
	INV.ORGENHNR AS INVESTIGATIONSORGUNITID,      
	AKPER.ORGID AS EXECUTIONSORGID,      
	AKPER.ORGENHNR AS EXECUTIONSORGUNITID,      
	STAT.XNUM7 AS STATISTICINDIVIDUALID,      
	STAT1.KLARTEXT AS STATISTICINDIVIDUAL,      
	STAT.XNUM8 AS STATISTICQUANTITYID,      
	STAT2.KLARTEXT AS STATISTICQUANTITY,      
	STAT.XNUM4 AS LAWSECTIONID,      
	STAT3.KLARTEXT AS LAWSECTION,      
	RES.KOD AS STATISTICAPPLICANTREASONID,      
	RES.KLARTEXT AS STATISTICAPPLICANTREASON,      
	RES.XTXT1 AS STATISTICAPPLICANTREASONTITLE,      
	RES.XTXT2 AS STATISTICAPPLICANTREASONCODE,      
	RES1.KOD AS STATISTICCOAPPLICANTREASONID,      
	RES1.KLARTEXT AS STATISTICCOAPPLICANTREASON,      
	RES1.XTXT1 AS STATISTICCOAPPLICANTREASONTITLE,      
	RES1.XTXT2 AS STATISTICCOAPPLICANTREASONCODE,      
	GAR.KLARTEXT AS VARDNHAV_T             
	FROM LD0P0E FOR SYSTEM_TIME CONTAINED IN (@StartTime, @EndTime) E
	INNER JOIN LD0V0I VI ON E.BESFATT = VI.HAND    
	OUTER APPLY (
	    SELECT DISTINCT  E.BESLUTNR, A.KUNDNUMMER, A.PNR
	    FROM LD0P0A A 
	    JOIN LD0P0C C ON C.AKTPNR = A.PNR
	    WHERE C.AEVERKNR = E.AEVERKNR
		
	) CT
	OUTER APPLY (
	    SELECT  DISTINCT E.BESLUTNR, A.KUNDNUMMER, A.PNR
	    FROM LD0P0A A 
	    JOIN LD0P0C20 C20 ON C20.AKTPNR = A.PNR
	    WHERE C20.INSNR = E.INSNR
		
	) CT20
	OUTER APPLY (
	    SELECT  DISTINCT E.BESLUTNR, A.KUNDNUMMER, A.PNR
	    FROM LD0P0A A 
	    JOIN LD0P0H H ON H.PNR = A.PNR
	    WHERE H.BESLUTNR = E.BESLUTNR
		
	) H
	
	--LEFT JOIN LD0V0H0T H ON H.BESLUTNR = E.BESLUTNR
	LEFT JOIN LD0P0A A ON A.PNR = H.PNR
	LEFT OUTER JOIN LD0V0Q01T Q01 ON Q01.KODTYP = 'BESLORSAK' AND E.ORSAK = Q01.KOD      
	LEFT OUTER JOIN LD0V0Q01T Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND E.BESLUT = Q01_2.KOD      
	LEFT OUTER JOIN LD0V0Q01T Q01_3 ON Q01_3.KODTYP = 'BESLORSAK' AND E.ORSAKMED = Q01_3.KOD      
	LEFT OUTER JOIN LD0V0Q01T  Q01_4 ON Q01_4.KODTYP = 'BESLUTTYP' AND E.BESLTYP = Q01_4.KOD      
	LEFT OUTER JOIN LD0VYC20T    AS L20   ON E.INSNR =L20.INSNR      
	LEFT OUTER JOIN LD0VYC20T     AS SERV   ON SERV.INSNR = E.INSNR      
	LEFT OUTER JOIN LD0VYCT     AS INV   ON  E.AEVERKNR= INV.AEVERKNR AND INV.TMARK = 0      
	LEFT OUTER JOIN LD0VYCT AS AKPER  ON E.AEVERKNR = AKPER.AEVERKNR AND AKPER.TMARK = 1      
	LEFT OUTER JOIN LD0V0I     AS PROF3  ON PROF3.HAND = INV.HAND      
	LEFT OUTER JOIN LD0V0I     AS PROF2  ON PROF2.HAND = L20.HAND      
	LEFT OUTER JOIN LD0V0I     AS PROF1  ON PROF1.Hand = AKPER.HAND      
	LEFT OUTER JOIN LD0P0J01    AS J01_SERV  ON J01_SERV.ORGENHNR = L20.ORGENHNR AND J01_SERV.ORGID = L20.ORGID      
	LEFT OUTER JOIN LD0V0J01    AS J01_INV  ON J01_INV.ORGENHNR =INV.ORGENHNR AND J01_INV.ORGID = INV.ORGID      
	LEFT OUTER JOIN LD0V0J01    AS J01_AKPER  ON J01_AKPER.ORGENHNR =AKPER.ORGENHNR AND J01_AKPER.ORGID = AKPER.ORGID       
	LEFT OUTER JOIN LD0V0Q04    AS STATGRPVAL ON STATGRPVAL.KODTYP = 'BESLUT' AND E.BESLUT = STATGRPVAL.KOD      
	LEFT OUTER JOIN LD0V0Q01T    AS T10   ON T10.KODTYP = 'STATGRUPP'   AND T10.XTXT1 = 'BESLUT'   AND T10.KOD = STATGRPVAL.STATGRP1             
	LEFT OUTER JOIN LD0V0Q01T    AS T11   ON T11.KODTYP = 'STATGRUPP'   AND T11.XTXT1 = 'BESLUT'   AND T11.KOD = STATGRPVAL.STATGRP2      
	LEFT OUTER JOIN LD0V0Q01T    AS T12   ON T12.KODTYP = 'STATGRUPP'   AND T12.XTXT1 = 'BESLUT'   AND T12.KOD = STATGRPVAL.STATGRP3      
	LEFT OUTER JOIN LD0V0Q01T    AS T13   ON T13.KODTYP = 'STATGRUPP'   AND T13.XTXT1 = 'BESLUT'   AND T13.KOD = STATGRPVAL.STATGRP4      
	LEFT OUTER JOIN LD0V0Q01T    AS T14   ON T14.KODTYP = 'STATGRUPP'   AND T14.XTXT1 = 'BESLUT'   AND T14.KOD = STATGRPVAL.STATGRP5      
	LEFT OUTER JOIN LD0V0Q01T    AS STAT   ON STAT.KODTYP = 'BESLUT' AND STAT.KOD = E.BESLUT      
	LEFT OUTER JOIN LD0V0Q01T    AS STAT1   ON STAT1.KOD = STAT.XNUM7 AND STAT1.KODTYP = 'SCBKODBESL'      
	LEFT OUTER JOIN LD0V0Q01T    AS STAT2   ON STAT2.KOD = STAT.XNUM8 AND STAT2.KODTYP = 'SCBKODBESM'      
	LEFT OUTER JOIN STAT3   ON STAT3.KOD = STAT.XNUM4   
	LEFT OUTER JOIN LD0V0Q01T    AS RES  ON RES.KOD = Q01.XNUM1 AND RES.KODTYP = 'SCBKODORS'      
	LEFT OUTER JOIN LD0V0Q01T    AS RES1  ON RES1.KOD = Q01_3.XNUM1 AND RES1.KODTYP = 'SCBKODORS'      
	LEFT OUTER JOIN LD0V0Q01T    AS GAR  ON GAR.KOD = E.VARDNHAV AND GAR.KODTYP = 'VARDHAVARE'
	INNER JOIN LD0FGetMaxEndTimeForDecisionAPI(@StartTime, @EndTime) b   
	 ON E.BESLUTNR = b.BESLUTNR AND E.ENDTIME = b.MaxEndTime  
	WHERE E.ENDTIME='9999-12-31 23:59:59.9999999' 
)    