update r
set r.CardNumber = Cast(isnull(evbase.AmbulanceCard,acr.AmbulanceCard)  as varchar(36))
from AKUZ.T_CDA_MSE_REFERRAL r
join akuz.T_EVENT_BASE evbase on r.EventBase = evbase.EventID
left join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = r.CardNote
where try_cast(r.CardNumber as uniqueidentifier) is null 
and (evbase.AmbulanceCard is not null or acr.AmbulanceCard is not null)


select eb.EventID, ISNULL(ac1.AmbulanceCardID, acr.AmbulanceCard) as AmbulanceCard, r.ProviderOrganization, r.SNILS, r.BirthTime
into #Events
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 uniqueidentifier)
left join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = r.CardNote
where eb.AmbulanceCard is null --and eb.EventDate >= '20241101'
group by eb.EventID, ISNULL(ac1.AmbulanceCardID, acr.AmbulanceCard), r.ProviderOrganization, r.SNILS, r.BirthTime

update e
set e.AmbulanceCard = (select top 1 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)
from #Events e
where e.AmbulanceCard is null and e.SNILS is not null

update eb
set eb.AmbulanceCard = e.AmbulanceCard
from AKUZ.T_EVENT_BASE eb
join #Events e on e.EventID = eb.EventID
where e.AmbulanceCard is not null

drop table #Events