--rpt.pan_EvnDeathSvid_Spisok_PG
with param as(
	select
    cast($1 as bigint) as p_lpu_id, 
    cast($2 as bigint) as p_medpersonal_id, 
    dbo.GetDiagCodeByID(cast($3 as bigint)) as p_begdiag_code, 
    dbo.GetDiagCodeByID(cast($4 as bigint)) as p_enddiag_code, 
    cast($5 as bigint) as p_sex_id, 
    cast($6 as bigint) as p_begperson_age, 
    cast($7 as bigint) as p_endperson_age, 
    cast($8 as bigint) as p_klareatype, 
    cast(to_timestamp($9,'dd.mm.yyyy') as timestamp) as p_begdate, 
    cast(to_timestamp($10,'dd.mm.yyyy') as timestamp) as p_enddate
)
select distinct
  ROW_NUMBER() OVER(ORDER BY rtrim(coalesce(' ' || Person.Person_SurName, '')) ||
    rtrim(coalesce(' ' || Person.Person_FirName, '')) || 
    rtrim(coalesce(' ' || Person.Person_SecName, ''))) as "Record_Num",
    
  rtrim(coalesce(' ' || Person.Person_SurName, '')) || rtrim(coalesce(' ' || Person.Person_FirName, '')) || 
    rtrim(coalesce(' ' || Person.Person_SecName, '')) as "Person_FIO",
    
  to_char(Person.Person_BirthDay, 'DD.MM.YYYY') as "BirthDay",
  Address.Address_Address as "Address_Address",
  AddDSvid.Address_Address as "AddDSvid",
  diag_mid.Diag_FullName as "FDiag",  -- #97805
  diag_iid.Diag_FullName as "Diag",
  diag_tid.Diag_FullName as "Patolog_Sost",
  diag_eid.Diag_FullName as "v_pr",
  coalesce(diag_eid.Diag_FullName, diag_mid.Diag_FullName,diag_iid.Diag_FullName) as "v7",
  diag_oid.Diag_FullName as "v11",
  DeathCause.DeathCause_Name as "DeathCause_Name",
  DeathPlace.DeathPlace_Name as "DeathPlace_Name",
  DeathSetCause.DeathSetCause_Name as "DeathSetCause_Name",
case	WHEN ((DeathSvid.DeathSvid_DeathDate is NOT NULL)) THEN  to_char(DeathSvid.DeathSvid_DeathDate,'DD.MM.YYYY')||' '||SUBSTRING(to_char(DeathSvid.DeathSvid_DeathDate,'DD.MM.YYYY'),1,5)
			WHEN ((DeathSvid.DeathSvid_DeathDate is NULL) and (DeathSvid.DeathSvid_DeathDateStr is Not NULL)) THEN DeathSvid.DeathSvid_DeathDateStr
			WHEN ((DeathSvid.DeathSvid_DeathDate is NULL) and (DeathSvid.DeathSvid_DeathDateStr is NULL) and (DeathSvid.DeathSvid_IsNoDeathTime=2)) THEN 'Дата смерти неизвестна'
end as "DeathSvid_DeathDate",  
 /* в соответствии с 89231*/
  DeathTrauma.DeathTrauma_Name as "DeathTrauma_Name",
  DeathSvid.DeathSvid_Num as "Nomer",
  KLAreaType_Name as "KLAreaType_Name"
from param
inner join DeathSvid on true    
inner join v_PersonState as Person   on Person.Person_id = deathsvid.Person_id
left join v_Address as Address on Address.Address_id = coalesce(Person.UAddress_id,Person.PAddress_id)
left join v_KLAreaType on Address.KLAreaType_id=v_KLAreaType.KLAreaType_id
left join v_Address as AddDSvid   on AddDSvid.Address_id = DeathSvid.Address_did
left join DeathCause on DeathCause.DeathCause_id = DeathSvid.DeathCause_id
left join DeathPlace on DeathPlace.DeathPlace_id = DeathSvid.DeathPlace_id
left join DeathTrauma on DeathTrauma.DeathTrauma_id = deathsvid.DeathTrauma_id 
left join v_DeathSetCause DeathSetCause on DeathSetCause.DeathSetCause_id = DeathSvid.DeathSetCause_id
left join v_Diag as diag_mid on diag_mid.Diag_id = DeathSvid.Diag_mid -- основная причина смерти
left join v_Diag as diag_iid   on diag_iid.Diag_id = DeathSvid.Diag_iid --непосредственная причина смерти
left join v_Diag as diag_eid   on diag_eid.Diag_id = DeathSvid.Diag_eid --внешние причины
left join v_Diag as diag_tid   on diag_tid.Diag_id = DeathSvid.Diag_tid  --патологическое состояние
left join v_Diag as diag_oid   on diag_oid.Diag_id = DeathSvid.Diag_oid  --прочие важные состояние
where 
  (1 = 1)
  and  (DeathSvid.DeathSvid_deleted = 1 or DeathSvid.DeathSvid_deleted is null)
  and ((DeathSvid.Lpu_id = p_Lpu_id) or (p_Lpu_id is null))
  and (cast( DeathSvid.DeathSvid_GiveDate as date) between p_begdate and p_enddate)
  and (coalesce(DeathSvid.DeathSvid_IsBad, 1) != 2 and coalesce(DeathSvid.DeathSvid_IsLose, 1) != 2)
  and (coalesce(DeathSvid.DeathSvid_IsActual,2)=2) -- #87864 добавлена проверка на актуальность св-ва  
  and ((Person.Sex_id = p_Sex_id) or (p_Sex_id is null))
  and ((Datediff('year', Person.Person_Birthday, coalesce(DeathSvid_DeathDate, GETDATE())) + 
    case when month(Person.Person_Birthday) > month(coalesce(DeathSvid_DeathDate, GETDATE()))
      or (month(Person.Person_Birthday) = month(coalesce(DeathSvid_DeathDate, GETDATE())) 
      and day(Person.Person_Birthday) > day(coalesce(DeathSvid_DeathDate, GETDATE())))
    then -1 else 0 end) between coalesce(p_begPerson_Age,0) and coalesce(p_endPerson_Age,200))
 and (
    (diag_mid.Diag_Code is not null and 
      ((diag_mid.Diag_Code between p_BegDiag_Code and p_EndDiag_Code)
       or (p_BegDiag_Code is null and p_EndDiag_Code is null))
    )
    or
    (diag_mid.Diag_Code is null and diag_tid.Diag_Code is not null and 
      ((diag_tid.Diag_Code between p_BegDiag_Code and p_EndDiag_Code)
      or (p_BegDiag_Code is null and p_EndDiag_Code is null))
    )
    or
    (diag_mid.Diag_Code is null and diag_tid.Diag_Code is null and diag_iid.Diag_Code is not null and
      ((diag_iid.Diag_Code between p_BegDiag_Code and p_EndDiag_Code)
      or (p_BegDiag_Code is null and p_EndDiag_Code is null))
    )  
      )
  and ((DeathSvid.MedPersonal_id = p_Medpersonal_id) or (p_Medpersonal_id is null))
  and (select DeathSvidType_id from DeathSvid D where D.Person_id =DeathSvid.Person_id  and (coalesce(DeathSvid.DeathSvid_IsBad, 1) != 2  and coalesce(DeathSvid.DeathSvid_IsLose, 1) != 2 and (coalesce(DeathSvid.DeathSvid_IsActual,2)=2) ) order by  DeathSvid_insDT desc limit 1) = DeathSvid.DeathSvidType_id
--	and DeathSvid_id = 3492
  and ((v_KLAreaType.KLAreaType_id = p_KLAreaType) or (p_KLAreaType is NULL))

