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

--https://jira.is-mis.ru/browse/PROMEDWEB-10597
-- Моск. обл. АРМ Министра. Смертность
with prm as (
select
'01.01.2019'::timestamp as BegDate,
cast(cast(GetDate() as date) as timestamp) as EndDate)

select distinct case
                  when L."МО КВС" = 'ГБУЗ МО "ОДИНЦОВСКАЯ ЦРБ"' then 'ГБУЗ МО «Одинцовская ОБ»'
                  else L."МО КВС"
                end "url",
       L."МО ОГРН" "ogrn",
       l.EvnPS_NumCard "kvs",
       l."Диагноз" "diag",
       l.Diag_Name "full_diag",
       l."Фамилия" "fam",
       l."Имя" "name",
       l."Отчество" "sec_name",
       l."Д.Р." "birthday",
       l."Дата поступления" as "date_in",
       l.EvnPS_updDT "date_updt",
       l.PrehospWaifRefuseCause_id,
       l.num "num"
from (
       select distinct L.Lpu_Nick as "МО КВС",
              L.Lpu_OGRN as "МО ОГРН",
              eps.EvnPS_NumCard,
              eps.PrehospWaifRefuseCause_id,
              diag.Diag_Code as "Диагноз",
              diag.Diag_Name,
              PS.Person_id as Cnt_person_id,
              PS.Person_SurName as "Фамилия",
              PS.Person_FirName as "Имя",
              PS.Person_SecName as "Отчество",
              (PS.Person_BirthDay) as "Д.Р.",
              eps.EvnPS_updDT,
              EPS.EvnPS_setDT as "Дата поступления",
              to_char(EPS.EvnPS_disDT, 'DD.MM.YYYY HH24:MI') as "Дата выписки",
              PS.Person_SurName || PS.Person_FirName || PS.Person_SecName || to_char(PS.Person_BirthDay, 'dd.MM.yyyy') as "id",
              ROW_NUMBER() over(partition by COALESCE(PS.Person_SurName, '') || COALESCE(PS.Person_FirName, '') || COALESCE(PS.Person_SecName, '') || to_char(COALESCE(PS.Person_BirthDay, '1900-01-01'), 'dd.MM.yyyy')
       order by eps.EvnPS_NumCard, EPS.EvnPS_setDT) num
       from dbo.v_Lpu L
            inner join dbo.v_EvnPS EPS on EPS.Lpu_id = L.Lpu_id and L.Lpu_begDate < dateadd('day', 1, (select enddate from prm)) and (L.Lpu_endDate >= (select begdate from prm)
 or L.Lpu_endDate is null) and COALESCE(L.Lpu_IsTest, 1) = 1 and EPS.LeaveType_id is null and EPS.EvnPS_disDate is null
            inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id
            left join Diag as diag on EPS.Diag_id = Diag.Diag_id
            join
            (
              select distinct k."Д.Р.2"
              from (
                     select distinct L.Lpu_Nick as "МО КВС",
                            L.Lpu_OGRN as "МО ОГРН",
                            eps.EvnPS_NumCard,
                            --eps.refus,
                            diag.Diag_Code as "Диагноз",
                            PS.Person_id as Cnt_person_id,
                            PS.Person_SurName as "Фамилия",
                            PS.Person_FirName as "Имя",
                            PS.Person_SecName as "Отчество",
                            (PS.Person_BirthDay) as "Д.Р.",
                            --to_char(EPS2.EvnPS_setDT, 'DD.MM.YYYY') as [Дата поступления",
                            to_char(EPS.EvnPS_disDT, 'DD.MM.YYYY HH24:MI') as "Дата выписки",
                            PS.Person_SurName || PS.Person_FirName || PS.Person_SecName || to_char(PS.Person_BirthDay, 'dd.MM.yyyy') as "Д.Р.2",
                            ROW_NUMBER() over(partition by COALESCE(PS.Person_SurName, '') || COALESCE(PS.Person_FirName, '') || COALESCE(PS.Person_SecName, '') || to_char(COALESCE(PS.Person_BirthDay, '1900-01-01'), 'dd.MM.yyyy')
                     order by eps.EvnPS_NumCard, EPS.EvnPS_setDT) num
                     from dbo.v_Lpu L
                          inner join dbo.v_EvnPS EPS on EPS.Lpu_id = L.Lpu_id and L.Lpu_begDate < dateadd('day', 1, (select enddate from prm)) and (L.Lpu_endDate >= (select begdate from prm)
 or L.Lpu_endDate is null) and COALESCE(L.Lpu_IsTest, 1) = 1 and EPS.LeaveType_id is null and EPS.EvnPS_disDate is null and eps.PrehospWaifRefuseCause_id is null
                          inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id
                          left join Diag as diag on EPS.Diag_id = Diag.Diag_id
                          --where  PS.Person_SurName+PS.Person_FirName='АКСЁНОВАНАТАЛЬЯ'
                   ) k
              where k.num > 1
            ) m on m."Д.Р.2" = PS.Person_SurName || PS.Person_FirName || PS.Person_SecName || to_char(PS.Person_BirthDay, 'dd.MM.yyyy')
     ) l



