select distinct *
from
(select  v_lpu.lpu_name as "ЛПУ"
		, v_lpu.lpu_id "Lpu_id"
		, evnps.evnps_numcard "EvnPS_NumCard"
		, coalesce(v_personstate.person_surname,'') as "Фамилия"
		, coalesce(v_personstate.person_firName,'') as "Имя"
		, coalesce(v_personState.person_secname,'') as "Отчество"
		, coalesce(to_char(v_personstate.person_birthday, 'dd.mm.yyyy'),'') as "Дата рождения" 
		, evn.evn_upddt::date - evn.evn_disdt::date as "Разница дни"
		, evn.Evn_disdt::date as "Дата выписки"
from EvnPS
left join LeaveType on LeaveType.LeaveType_id = EvnPS.LeaveType_id
inner join evn on EvnPS.Evn_id = evn.Evn_id
left  join Diag as diag on EvnPS.Diag_id = Diag.Diag_id --основной диагноз
inner join PersonEvn on evn.PersonEvn_id = PersonEvn.PersonEvn_id
inner join v_PersonState on v_PersonState.Person_id = PersonEvn.Person_id
inner join v_Lpu on evn.Lpu_id = v_Lpu.Lpu_id 
							and coalesce(Lpu_IsTest, 1) = 1     
							and Lpu_begDate::date <= current_date --+ INTERVAL '1 year'
							and (Lpu_endDate::date >= '2020-01-01'::date or Lpu_endDate is null)
inner join LpuSection on EvnPS.LpuSection_id = LpuSection.LpuSection_id
left join Prehosptype as pht on EvnPS.PrehospType_id = pht.PrehospType_id --экстренно или планово
left join DiagSetPhase on EvnPS.DiagSetPhase_pid = DiagSetPhase.DiagSetPhase_id 
left join PrehospArrive as pha on EvnPS.PrehospArrive_id = pha.PrehospArrive_id 
where 
	(diag.diag_code  like 'j1[2-8]%' or diag.diag_code  like '%B34.2%' or diag.diag_code  like '%B33.8%' or  diag.diag_code  like '%Z11.5%'or
	diag.diag_code  like '%Z03.8%' or diag.diag_code  like '%Z22.8%' or diag.diag_code  like '%Z20.8%' or   diag.diag_code  like '%U07%')
	and evn.Evn_disDT::date >= (current_date-1) and evn.Evn_disDT::date <= (current_date-1)
	and  LeaveType.LeaveType_Name in ('Выписан','Переведен на другой профиль коек' , 'Умер', 'Лечение прервано по инициативе пациента'
	, 'Самовольно прерванное лечение', 'Лечение прервано по инициативе ЛПУ','Переведён на другой профиль коек')
	and v_Lpu.lpu_id not in ('9990010243','9990010244','13100948','13101242')
) as new_table
	

