update AKUZ.T_CDA_MSE_REFERRAL r
set CardNumber = Cast(acr.AmbulanceCard  as varchar(36))
from AKUZ.T_AMBULANCE_CARD_RECORD acr 
where acr.MedicalRecordID = r.CardNote and LENGTH(r.CardNumber) < 36;

DROP TABLE IF EXISTS tempTbForEhrs;
CREATE TEMP TABLE tempEvents(
    EventID uuid
  , AmbulanceCard uuid
  , ProviderOrganization uuid
  , SNILS varchar(14)
  , BirthTime timestamp without time zone
);

insert into tempEvents
select eb.EventID, coalesce(ac1.AmbulanceCardID, acr.AmbulanceCard) as AmbulanceCard, r.ProviderOrganization, r.SNILS, r.BirthTime
from AKUZ.T_EVENT_BASE eb
join AKUZ.T_CDA_MSE_REFERRAL r on r.EventBase = eb.EventID
left join AKUZ.T_AMBULANCE_CARD ac1 on ac1.AmbulanceCardID = cast(r.CardNumber as uuid)
left join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = r.CardNote
where eb.AmbulanceCard is null
group by eb.EventID, coalesce(ac1.AmbulanceCardID, acr.AmbulanceCard), r.ProviderOrganization, r.SNILS, r.BirthTime;

update tempEvents e
set AmbulanceCard = (select ac.AmbulanceCardID 
							from AKUZ.T_AMBULANCE_CARD ac 
							join AKUZ.T_PATIENT p on p.PatientID = ac.Patient
							where ac.CardLpu = e.ProviderOrganization and p.SNILS = e.SNILS and p.Birthday = e.BirthTime limit 1)
where e.AmbulanceCard is null and e.SNILS is not null;

update AKUZ.T_EVENT_BASE eb
set AmbulanceCard = e.AmbulanceCard
from tempEvents e
where e.EventID = eb.EventID and e.AmbulanceCard is not null;

drop table tempEvents;