insert into rep38_results(admissionid, vargroup, vartype, vartime, varenttime, varstrvalue, vardoublevalue, vartimevalue,validated)
select patientid, Labogroep, v.name, sampletime, entertime, stringvalue, varvalue,null,false
from (
	select lc.patientid, lm.Labogroep, lm.name, lc.sampletime, lc.entertime, lc.stringvalue, lc.varvalue, 
		lc.resultid, lc.ResultNo,
		row_number() over(partition by lc.patientid, lc.variableid_nieuw, lc.SampleTime order by lc.ResultID, lc.ResultNo desc) as rang
	from rep38_cohort coh
	join izisprod.P_GeneralData gd on 
		gd.PatientID = cast(coh.admissionid as integer) and
		gd.Status >= 4 and gd.status <> 5
	join izisprod.U_PV_LabCorrectValues lc on 
		lc.PatientID = gd.patientid and
		BITAND(lc.status, 8) = 8 and
		BITAND(lc.status, 2) <> 2 and
		lc.sampletime > PARSETIMESTAMP('2013-01-01','yyyy-MM-dd') and
		lc.sampletime between coh.studystart and coh.studyend
	join izisprod.U_P_LabMapping lm on 
		lm.labogroep in ('WBC-b','INR','crp-s','Thrombocyten','Lactaat-s') and
		lm.VariableID = lc.variableid_oud
	UNION ALL
	select lc.patientid, lm.Labogroep, lm.name, lc.sampletime, lc.entertime, lc.stringvalue, lc.varvalue, 
		lc.resultid, lc.ResultNo,
		row_number() over(partition by lc.patientid, lc.variableid_nieuw, lc.SampleTime order by lc.ResultID, lc.ResultNo desc) as rang
	from rep38_cohort coh
	join iziswh.P_GeneralData gd on gd.PatientID = cast(coh.admissionid as integer)
	join iziswh.U_PV_LabCorrectValues lc on 
		lc.PatientID = gd.patientid and
		BITAND(lc.status, 8) = 8 and
		BITAND(lc.status, 2) <> 2 and
		lc.sampletime > PARSETIMESTAMP('2013-01-01','yyyy-MM-dd') and
		lc.sampletime between coh.studystart and coh.studyend
	join izisprod.U_P_LabMapping lm on 
		lm.labogroep in ('WBC-b','INR','crp-s','Thrombocyten','Lactaat-s') and
		lm.VariableID = lc.variableid_oud
) v
where v.rang = 1
order by patientid, Labogroep, SampleTime;