--http://bi.mz.mosreg.ru/#edit/etl/115

select distinct COALESCE(v_PersonState.Person_SurName, ' ') as "Фамилия",
       COALESCE(v_PersonState.Person_FirName, ' ') as "Имя",
       COALESCE(v_PersonState.Person_SecName, ' ') as "Отчество",
       cast (v_PersonState.Person_BirthDay as date) as "Дата_рождения",
       trim(COALESCE((v_PersonState.Polis_Ser || ' '), '') || COALESCE(Polis_Num, '')) as "Полис",
       COALESCE(v_Lpu.Lpu_Name, ' ') as "ЛПУ",
       COALESCE(v_Lpu.Lpu_Nick, ' ') as "Lpu_Nick",
       COALESCE(LpuSection.LpuSection_Name, ' ') as "Отделение",
       COALESCE(EvnPS_NumCard, ' ') as "Номер_КВС",
       cast (EvnPS.Evn_setDT as date) as "Дата_госпитализации",
       Diag.Diag_Code || ' - ' || diag.Diag_Name as "Диагноз",
       COALESCE(pht.PrehospType_Name, ' ') as "Тип_госпитализации",
       COALESCE(DiagSetPhase.DiagSetPhase_Name, ' ') as "Состояние",
       COALESCE(prc.PrehospWaifRefuseCause_Name, ' ') 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 <= dateadd('year', 1, dbo.tzGetDate()) and (Lpu_endDate >= '01.01.2020'::date or Lpu_endDate is null)
     inner join LpuSection on EvnPS.LpuSection_id = LpuSection.LpuSection_id
     left join Diag as diag1 on EvnPS.Diag_aid = Diag1.Diag_id --Основной паталогоанатомический диагноз
     left join Diag as diag2 on EvnPS.Diag_did = Diag2.Diag_id --Основной диагноз направившего учреждения
     left join Diag as diag3 on EvnPS.Diag_eid = Diag3.Diag_id --другие заболевания матери
     left join Diag as diag4 on EvnPS.Diag_pid = Diag4.Diag_id --приемное отделение
     left join Diag as diag5 on EvnPS.Diag_spid = Diag5.Diag_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 PrehospWaifRefuseCause as prc on EvnPS.PrehospWaifRefuseCause_id = prc.PrehospWaifRefuseCause_id
where cast (EvnPS.Evn_setDT as date) >= '2021-04-30' and
      cast (EvnPS.Evn_setDT as date) < '2021-05-14'
