---Перечень умерших пациентов в стационаре МО иногородних
with EPS as 
(
  select 
    ROW_NUMBER()OVER(partition by PS.Person_id order by EPS.EvnPS_setDT desc) as rnum,
    L.Lpu_Nick,
	L.Lpu_OGRN,
	L.PAddress_Address,l.UAddress_Address,
	EPS.EvnPS_id,
	EPS.EvnPS_NumCard,
	EPS.EvnPS_setDT,
	EPS.EvnPS_setDate,
	EPS.EvnPS_disDT,
	EPS.EvnPS_disDate,
	EPS.LpuSection_id,
	PS.Person_SurName,
	PS.Person_FirName,
	PS.Person_SecName,
	PS.Person_BirthDay,
	PS.PAddress_id,
	PS.UAddress_id,
	EPS.Diag_pid,
	adr.address_address as "MANUAL_INP"
	,adrf.address_address as "F_MANL_INP"
	,kar.kladr_code as "KLADR_CODE"--"Код Кладр рег"
	,klc.klcountry_name as "COUNTRY"
	,kar.klarea_name||' '||socr.klsocr_name as "AREA"
	,socr2.klsocr_name as klsocr_name1
	,kar2.klarea_name as "CITY"
	,socr3.klsocr_name as klsocr_name2
	,kls.klstreet_name as "STREET"
	,adr.address_house as "HOUSE"
	,adr.address_flat as "FLAT"
	,karf.kladr_code  as "F_KLADR"
	,klcf.klcountry_name as "F_COUNTRY"
	,karf.klarea_name||' '||socrf.klsocr_name as "F_AREA"
	,kar2f.klarea_name as "F_CITY"
	,socr3f.klsocr_name as klsocr_name3
	,klsf.klstreet_name  as "F_STREET"
	,adrf.address_house as "F_HOUSE"
	,adrf.address_flat as "F_FLAT"
	,diag.Diag_Code || ' - ' || diag.Diag_Name  as  "Диагноз"
	,coalesce(fc.FamilyContact_FIO, ' ') as FamilyContact_FIO
	,coalesce(fc.FamilyContact_Phone, ' ') as FamilyContact_Phone
	--свид о смерти
	,cast(DS.DeathSvid_insDT as date) as "Дата создания свидетельства"
	,ds.DeathSvid_GiveDate
	,DS.DeathSvid_Num as "Id свидетельства"
	,eps.EvnPS_updDT
	--,case when DS.DeathSvid_IsActual = 2 then 'Да' else 'Нет' end as "Свидетельство актуально"
	--,case when DS.DeathSvid_IsBad = 2 then 'Да' else 'Нет' end as "Свидетельство испорчено"
	--,case when DS.DeathSvid_IsLose = 2 then 'Да' else 'Нет' end as "Свидетельство утеряно"
	--,PntDS.PntDeathSvid_id "Id перинат.свидетельства"
from dbo.v_Lpu L   
  inner join dbo.v_EvnPS EPS   on EPS.Lpu_id = L.Lpu_id 
	and L.Lpu_begDate::date < current_date::date
	and (L.Lpu_endDate::date >= '2020-10-14' or L.Lpu_endDate is null)
	and coalesce(L.Lpu_IsTest, 1) = 1
    and EPS.EvnPS_disDate between '2020-10-14' and current_date::date
    and EPS.LeaveType_id between 685 and 686 
  inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id	
  left join address as adr on ps.uaddress_id = adr.address_id --адрес регистрации
  left join address as adrf on ps.PAddress_id = adrf.address_id --адрес фактич
  left join klcountry as klc on adr.klcountry_id = klc.klcountry_id
  left join klarea as kar on adr.klrgn_id = kar.klarea_id and kar.klarealevel_id='1' --область
  left join klarea as kar2 on adr.klcity_id = kar2.klarea_id and kar2.klarealevel_id='3' --город
  left join klsocr as socr on kar.klsocr_id = socr.klsocr_id
  left join klsocr as socr2 on kar2.klsocr_id = socr2.klsocr_id
  left join klstreet as kls on adr.klstreet_id = kls.klstreet_id
  left join klsocr as socr3 on kls.klsocr_id = socr3.klsocr_id
  left join klcountry as klcf on adrf.klcountry_id = klcf.klcountry_id
  left join klarea as karf on adrf.klrgn_id = karf.klarea_id and karf.klarealevel_id='1' --область
  left join klarea as kar2f on adrf.klcity_id = kar2f.klarea_id and kar2f.klarealevel_id='3' --город
  left join klsocr as socrf on karf.klsocr_id = socrf.klsocr_id
  left join klsocr as socr2f on kar2f.klsocr_id = socr2f.klsocr_id
  left join klstreet as klsf on adrf.klstreet_id = klsf.klstreet_id
  left join klsocr as socr3f on klsf.klsocr_id = socr3f.klsocr_id
  left  join Diag as diag   on EPS.Diag_id = Diag.Diag_id --основной диагноз
  left join FamilyContact as fc on fc.EvnPS_id = EPS.EvnPS_id
  --cвид о смерти
left join dbo.v_DeathSvid DS on DS.Person_id = ps.Person_id 
left join dbo.v_DeathSvidType DST on DST.DeathSvidType_id = DS.DeathSvidType_id  
left join dbo.v_Sex S on S.Sex_id = PS.Sex_id
--left join dbo.v_Diag D on D.Diag_id = coalesce(Diag_mid, Diag_tid, Diag_iid)
left join v_DeathPlace dp on dp.DeathPlace_id=ds.DeathPlace_id
left join dbo.v_PntDeathSvid PntDS on PntDS.Lpu_id = L.Lpu_id  --перенатальное свидетельство о смерти
)
select distinct
  EPS.Person_SurName as "Фамилия",
  EPS.Person_FirName as "Имя",
  EPS.Person_SecName as "Отчество",
  EPS.Person_BirthDay  as "Д.Р.",
  EPS.FamilyContact_FIO as "ФИО родственника",
  EPS.FamilyContact_Phone as "Телефон родственника",
  cast(EPS.EvnPS_disDT as date) as "Дата смерти",
  EPS. "COUNTRY" as "Страна регистрации",
  EPS. "AREA" as "Регион регистрации",
  case when eps."AREA" like 'МОСКОВСКАЯ ОБЛАСТЬ' or  eps."AREA" is null  then 1 else 0 end "Признак области",
  EPS."MANUAL_INP" as "Адрес регистрации",
  EPS.EvnPS_disDT as "Дата и время смерти",
  EXTRACT(year FROM age(EPS.EvnPS_disDT,EPS.Person_BirthDay)) :: int as  "Возраст",
 -- cast(datediff(MONTH,  cast(EPS.Person_BirthDay as date),  EPS.EvnPS_disDT)/12.0 as decimal (6,1)) as "Возраст",
  eps.Диагноз as "Диагноз",
  --LS.LpuSection_FullName as "Отделение, где умер пациент",
    EPS.Lpu_Nick as "МО КВС",
	eps.PAddress_Address,
	EPS."Дата создания свидетельства",
	EPS."Id свидетельства",
	eps.EvnPS_updDT
	--EPS."Свидетельство актуально",
	--EPS."Свидетельство испорчено",
	--EPS."Свидетельство утеряно"
from EPS  
/*
outer apply
(
  select top 1 
    ES.EvnSection_id,
	ES.LpuSection_id
  from dbo.v_EvnSection ES   
  where
    ES.EvnSection_pid = EPS.EvnPS_id 
  order by
    isnull(ES.EvnSection_IsPriem, 1),
    ES.EvnSection_setDT desc,
	ES.EvnSection_disDT desc
) ESLast

left join dbo.v_LpuSection_all LS   on LS.LpuSection_id = ESLast.LpuSection_id
left join dbo.v_LpuUnit LU   on LU.LpuUnit_id = LS.LpuUnit_id
left join dbo.v_LpuUnitType LUT   on LUT.LpuUnitType_id = LU.LpuUnitType_id*/
where 
  EPS.rnum = 1
  --and EPS.Lpu_Nick like '%дубнен%'
 --and  EPS.Person_SurName='скромнов'
  --and EPS."AREA" not in ('МОСКОВСКАЯ ОБЛАСТЬ')
	order by 7 desc
