--Отчет по всем госпитализированным  --OK --для направлений из поликлиники
select distinct
--dir.evndirection_id,
    Lpu_s.Lpu_Name lpu_s_name,
	Lpu.lpu_name "Юридическое лицо",
	Lpu.Lpu_Ogrn "ОГРН",
	lpusection.LpuSection_Name,
	coalesce( (select LpuSectionBedProfile_Name 
	from fed.LpuSectionBedProfileLink LSBPL 
	inner join V_LpuSectionBedProfile lsbp  on lsbp.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id
	where LSBPL.LpuSectionBedProfileLink_id = es.LpuSectionBedProfileLink_fedid ), 'Не указан') "Профиль койки",
	lpusection.LpuSectionProfile_Name "Профиль отделения",
	PS.Person_surname "Фамилия",
	PS.Person_firname "Имя",
	PS.Person_secname "Отчество",
	PS.Person_birthday "Дата рождения",
	eps.Person_id,
	ps.person_snils::text,
	D.Diag_code "Диагноз по МКБ-10",
	EPS.EvnPS_setDT  "Дата и время поступления",
	LT.LeaveType_Name "Исход госпитализации",
    EPS.EvnPS_disDT "Дата выписки",
	pht.PrehospType_Name "Тип госпитализации",
	coalesce(case when dir.DirType_id in ('1', '5') then dir.EvnDirection_Num else null end, eps.EvnDirection_Num) as "Айди электронного направления",
	coalesce(case when dir.DirType_id in ('1', '5') then dir.EvnDirection_setDate else null end,EPS.EvnDirection_setDT) as "Дата направления",
	eps.EvnDirection_id as "EvnDirection_id",
	eps.EvnDirection_Num as "EvnDirection_Num",
	EPS.EvnDirection_setDT as "EvnDirection_setDT",
	dir.EvnDirection_Num "dir_EvnDirection_Num",
	dir.EvnDirection_setDate "dir_EvnDirection_setDate"
from ((
  select 
    L.Lpu_id,
	L.Lpu_Name,
	L.Lpu_Ogrn
  from dbo.v_Lpu L 
  where 
    coalesce(L.Lpu_IsTest, 1) = 1 )) as Lpu  
	inner join v_EvnPS as EPS  on Lpu.Lpu_id =EPS.lpu_id
	inner join v_EvnSection as ES  on ES.EvnSection_pid = EPS.EvnPS_id
		and ES.EvnSection_Count-1 = ES.EvnSection_index	
		and EPS.PrehospWaifRefuseCause_id is null
	inner join v_lpusection as lpusection  on lpusection.lpusection_id =ES.lpusection_id
	inner join v_LpuUnit LU  on LU.LpuUnit_id = lpusection.LpuUnit_id
		and LU.LpuUnitType_SysNick in ('stac')
	 -- and (lpusection.LpuUnitType_SysNick = 'stac')
	inner join v_PersonState as PS  on EPS.Person_id = PS.Person_id
	--left join dbo.v_Sex S  on S.Sex_id = PS.Sex_id
	inner join v_Diag as D  on D.Diag_id = coalesce(EPS.Diag_pid,ES.Diag_id)
	--left join PrehospArrive as pha  on EPS.PrehospArrive_id = pha.PrehospArrive_id 
	left join v_LeaveType LT  on LT.LeaveType_id = EPS.LeaveType_id
	--left join v_EvnOtherLpu EOL  on EOL.EvnOtherLpu_pid = ES.EvnSection_id
	--left join v_Lpu lpuLeave  on lpuLeave.Lpu_id =EOL.lpu_oid
	--left join v_Org as Org   on Org.Org_id = EOL.Org_oid
	--left join v_EvnLeave EL  on EL.EvnLeave_pid = ES.EvnSection_id
	--left join v_EvnDie ED  on ED.EvnDie_pid = ES.EvnSection_id 
	--left join v_EvnOtherStac EOS  on EOS.EvnOtherStac_pid = ES.EvnSection_id
	--left join v_EvnOtherSectionBedProfile EOSBP  on EOSBP.EvnOtherSectionBedProfile_pid = ES.EvnSection_id
	--left join ResultDesease  on ResultDesease.ResultDesease_id = COALESCE(EL.ResultDesease_id, ED.ResultDesease_id, EOL.ResultDesease_id,EOS.ResultDesease_id)
	--left join LeaveCause   on LeaveCause.LeaveCause_id = COALESCE(EL.LeaveCause_id, EOL.LeaveCause_id, EOS.LeaveCause_id)
	--left join dbo.Address as Addr  on Addr.Address_id = coalesce(PS.PAddress_id, PS.UAddress_id)
	left join Prehosptype as pht  on EPS.PrehospType_id = pht.PrehospType_id --экстренно или планово
	left join v_EvnDirection as dir  on eps.EvnDirection_id = dir.EvnDirection_id
	left join DirType as DirType  on  dir.DirType_id = DirType.DirType_id
	left join ((
  select 
    L.Lpu_id,
	L.Lpu_Name,
	L.Lpu_Ogrn
  from dbo.v_Lpu L 
  where 
    coalesce(L.Lpu_IsTest, 1) = 1 )) as Lpu_s   on dir.lpu_sid=Lpu_s.Lpu_id
where 
	cast(EPS.EvnPS_setDate as date) between (now()-interval '30 days' )::date and now()::date--30
