with params as(
	select
		cast('frm12_2020' as varchar) as p_repdiag_sysnick,
		cast(1500 as bigint) as p_repdiag_part,
		cast(0 as bigint) as p_begage,
		cast(3 as bigint) as p_endage,
		cast($1 as bigint) as p_lpu_id,
		cast($2 as varchar) as p_lpufilialslist,
		cast($3 as bigint) as p_lpubuilding_id,
		cast($4 as bigint) as p_onlypersoncard_yn,
		cast($5 as timestamp) as p_begdate,
		cast($6 as timestamp) as p_enddate,
		cast($7 as bigint) as p_isselo,
		cast($8 as bigint) as p_isguzonly
),
Lpu_W as 
(
  select 
    L.Lpu_id,
    coalesce(L.Lpu_pid, L.Lpu_id) as Lpu_pid,
	dbo.GetRegion() as Region_id
  from params
	inner join rpt.GetLpu_succession(p_lpu_id,p_begdate,p_enddate) L on 1 = 1
	left join v_lpu L1 on l1.lpu_id=l.lpu_id
  inner join dbo.Org O on L1.Org_id = O.Org_id
    and (l.Lpu_id = p_Lpu_id 
	  or (p_Lpu_id is null or p_OnlyPersonCard_YN = 2)
	  or l.Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly or p_OnlyPersonCard_YN = 2)
),

LpuSection_W as 
(
  select 
    L.Lpu_id,
    LS.LpuSection_id,
    LS.LpuSection_Name,
    LS.LpuSection_Code
  from dbo.LpuSection LS
	left join params on 1=1
  left join dbo.LpuUnit LU on LS.LpuUnit_id = LU.LpuUnit_id
  left join dbo.LpuBuilding LB on LU.LpuBuilding_id = LB.LpuBuilding_id
  left join Lpu_W as L on LB.Lpu_id = L.Lpu_id 
  where 
    1 = 1
	and (LS.LpuSection_Deleted = 1 or LS.LpuSection_Deleted is null)
	and (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null or p_OnlyPersonCard_YN = 2)
	and (',' || p_LpuFilialsList || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_LpuFilialsList is null or p_OnlyPersonCard_YN = 2) 
	and (L.Lpu_id > 0 or p_OnlyPersonCard_YN = 2)
),

LpuRegion_W as 
(
  select 
    L.Lpu_id,
    LR.LpuRegion_id
  from dbo.v_Lpu L 
	left join params on 1=1
  inner join dbo.Org O on L.Org_id = O.Org_id
    and p_OnlyPersonCard_YN = 2
    and (Lpu_id = p_Lpu_id 
	  or p_Lpu_id is null and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly)
    and L.Lpu_begDate <= p_EndDate + interval '1 year'
    and coalesce(L.Lpu_endDate, p_BegDate) >= p_BegDate 
  inner join dbo.v_LpuRegion LR on LR.Lpu_id = L.Lpu_id
  left join dbo.LpuSection LS on LS.LpuSection_id = LR.lpusection_id
  left join dbo.LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
  left join dbo.LpuBuilding LB on LB.LpuBuilding_id = LU.LpuBuilding_id
  where 
    (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
    and (',' || p_LpuFilialsList || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_LpuFilialsList is null)
),

PersonCard_W as
(
  select
    PC.Person_id,
	PC.Lpu_id,
	PC.LpuAttachType_id,
	PC.LpuRegion_id,
	cast(cast(PC.PersonCard_begDate as date) as timestamp) as PersonCard_begDate,
	cast(cast(PC.PersonCard_endDate as date) as timestamp) as PersonCard_endDate,
	PC.CardCloseCause_id
  from LpuRegion_W as LR
	left join params on 1=1
  inner join dbo.v_PersonCard_all PC on PC.LpuRegion_id = LR.LpuRegion_id
    and PC.LpuAttachType_id = 1
    and PC.PersonCard_begDate < p_EndDate + interval '1 day'
    and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') >= p_EndDate + interval '1 day' 
      or PC.CardCloseCause_id = 2 and PC.PersonCard_endDate >= p_BegDate and PC.PersonCard_endDate < p_EndDate + interval '1 day') -- 2. Смерть 
),

PersonState_W as 
(
  select
    PS.Person_id,
    PS.Person_Age,
    PS.Person_BirthDay,
    PS.Sex_id
  from
  (
    select 
      PS.Person_id,
      cast(cast(PS.Person_BirthDay as date) as timestamp) as Person_BirthDay,
      PS.Sex_id,
      date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) as Person_Age
    from dbo.v_PersonState PS
	left join params on 1=1
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
    where
	  (rpt.workage(PS.Person_BirthDay, PS.Sex_id, p_EndDate) = 1 and p_RepDiag_Part = 4000
	  or p_RepDiag_Part in (1000, 2000, 5000, 2, 1500, 1600, 3000))
	  and (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
  ) PS 
	left join params on 1=1
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id
	and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
  where
    (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
),

EvnVizitDispDopOrp_W as -- dbo.v_EvnVizitDispDop и dbo.v_EvnVizitDispOrp
(
  select
    CAST(CAST(EVD.Evn_setDT AS DATE) AS timestamp) as EvnVizitDispDopOrp_setDate,
    EVD.Evn_id as EvnVizitDispDopOrp_id, 
		EVD.Evn_pid as EvnVizitDispDopOrp_pid,
		EVD.Lpu_id,
    EVD.MedStaffFact_id,
    EVD.MedPersonal_id,
    EVD.LpuSection_id,
		EVD.DopDispInfoConsent_id,
		EVD.Diag_id,
		EVD.DopDispDiagType_id
  from dbo.EvnVizitDisp EVD 
	left join params on 1=1
  where 
    EVD.EvnClass_id between 14 and 15
	and EVD.Evn_setDT between date_trunc('year', p_BegDate) and p_EndDate + interval '1 day'
	and EVD.DopDispDiagType_id between 1 and 2
	and (EVD.Evn_deleted = 1 or EVD.Evn_deleted is null)
),

EPLDisp as
(
  select --distinct
    - EPLD.Person_id as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая                                      
	EPLD.Person_id, 
    EPLD.Sex_id,
	EPLD.Person_BirthDay,

    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	then dbo.AgeYMD_pg(EPLD.Person_BirthDay, EvnPLDisp_setDate, 1) 
	else date_part('year', p_EndDate) - date_part('year', EPLD.Person_BirthDay) end as AgeY,

    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	  and dbo.AgeYMD_pg(EPLD.Person_BirthDay, EvnPLDisp_setDate, 2) = 0
	  then 2 else 1 end ::int4 as IsLessMonth,

	D.Diag_Code,
	D.Diag_id,
    case when EVD.DopDispDiagType_id = 1 then 3 else 2 end::int4 as DeseaseType_Code,-- select * from DopDispDiagType
    LpuP.Lpu_id,
    LpuP.Lpu_pid,
	EPLD.DispClass_id
  from Lpu_W as LpuP
	left join params on 1=1
  inner join
  ( 
    select
	  EPLD.EvnClass_id, 
	  EPLD.Evn_id AS EvnPLDisp_id, 
	  CAST(CAST(EPLD.Evn_setDT AS DATE) AS timestamp) AS EvnPLDisp_setDate,
	  CAST(CAST(EPLD.Evn_disDT AS DATE) AS timestamp) AS EvnPLDisp_disDate,
	  EPLD.Lpu_id,
	  EPLD.DispClass_id,
	  EPLD.Person_id,
		PS.Person_BirthDay,
		PS.Sex_id,
		PS.PAddress_id,
		PS.UAddress_id
	from dbo.EvnPLDisp EPLD
	left join params on 1=1
  inner join v_PersonState as PS on PS.Person_id = EPLD.Person_id  
    and (p_EndAge > 3 or dbo.AgeYMD_pg(PS.Person_BirthDay, CAST(CAST(EPLD.Evn_setDT AS DATE) AS timestamp), 1) between 0 and 2) --p_EndAge = 3 - это таблица 1500
	  and date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) between p_BegAge and p_EndAge
	  and (rpt.workage(PS.Person_BirthDay, PS.Sex_id, p_EndDate) = 1 and p_RepDiag_Part = 4000
	  or p_RepDiag_Part in (1000, 2000, 5000, 2, 1500, 1600, 3000))
	where 
	  EPLD.EvnClass_id IN (7,8,9,101,103,104)
	  and EPLD.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPLD.EvnPLBase_IsFinish = 2
	  and (p_EndAge >= 18 and EPLD.DispClass_id in (1,2, 5) 
	    or p_BegAge <= 18 and EPLD.DispClass_id in (6,9,10,11,12, 3,4,7,8))
	  and (EPLD.Evn_Deleted = 1 or EPLD.Evn_Deleted is null)  
  ) EPLD on EPLD.Lpu_id = LpuP.Lpu_id
    and EPLD.EvnPLDisp_disDate between p_BegDate and p_EndDate
  left join PersonCard_W as PC on PC.Person_id = EPLD.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = EPLD.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = EPLD.UAddress_id
  inner join EvnVizitDispDopOrp_W as EVD on EVD.EvnVizitDispDopOrp_pid = EPLD.EvnPLDisp_id
    and EVD.EvnVizitDispDopOrp_setDate between date_trunc('year', p_BegDate) and p_EndDate
	and EVD.Diag_id not between 10880 and 11502
    and EVD.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
	and EVD.DopDispDiagType_id between 1 and 2
  inner join LpuSection_W as LS on LS.LpuSection_id = EVD.LpuSection_id	
  inner join dbo.Diag D on D.Diag_id = EVD.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  left join dbo.v_DopDispInfoConsent DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
  left join dbo.v_SurveyTypeLink STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
  left join dbo.v_SurveyType ST on ST.SurveyType_id = STL.SurveyType_id
  where
    (lower(ST.SurveyType_Name) like '%осмотр%врач%' or EPLD.DispClass_id in (4,8,11,12)) -- select * from dbo.v_DispClass 
		and (coalesce(Addr1.KLAreaType_id,Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
),
EPL as
(
  select --distinct
    case when DT.DeseaseType_Code = 1 
	then EPL.Evn_id 
	else - PS.Person_id end as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая
      
    PS.Person_id,
    PS.Sex_id,

    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	then dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 1) 
	else date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) end as AgeY,
    
    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	  and dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 2) = 0 
      then 2 else 1 end ::int4 as IsLessMonth,
    
		D.Diag_Code,
		D.Diag_id,
    DT.DeseaseType_Code,
    LpuP.Lpu_id,
    LpuP.Lpu_pid,
    null::int8 as DispClass_id,
    left(DConcid.Diag_Code, 3) ::varchar as DConcid_Code
  from dbo.EvnPL EPL
	left join params on 1=1
  inner join LpuSection_W as LS on LS.LpuSection_id = EPL.LpuSection_id	
  left join dbo.Diag DConcid on DConcid.Diag_id = EPL.Diag_concid
  inner join dbo.Diag D on D.Diag_id = EPL.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  inner join dbo.v_DeseaseType DT on DT.DeseaseType_id = EPL.DeseaseType_id 
	inner join Lpu_W as LpuP on EPL.Lpu_id = LpuP.Lpu_id
  inner join v_PersonState as PS on PS.Person_id = EPL.Person_id
    and (p_EndAge > 3 or dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 1) between 0 and 2) --p_EndAge = 3 - это таблица 1500
	  and date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) between p_BegAge and p_EndAge
	  and (rpt.workage(PS.Person_BirthDay, PS.Sex_id, p_EndDate) = 1 and p_RepDiag_Part = 4000
	  or p_RepDiag_Part in (1000, 2000, 5000, 2, 1500, 1600, 3000))
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
	where (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		and EPL.EvnClass_id IN (3,6) 
	  and EPL.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPL.EvnPLBase_IsFinish = 2
	  and (EPL.Evn_deleted = 1 or EPL.Evn_deleted is null)
	and EPL.Diag_id not between 10880 and 11502
	and EPL.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
	
	union all
	
  select --distinct
    case when DT.DeseaseType_Code = 1 
	then EPL.Evn_id 
	else - PS.Person_id end as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая
      
    PS.Person_id,
    PS.Sex_id,

    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	then dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 1) 
	else date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) end as AgeY,
    
    case when p_EndAge = 3 --p_EndAge = 3 - это таблица 1500
	  and dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 2) = 0 
      then 2 else 1 end ::int4 as IsLessMonth,
    
		D.Diag_Code,
		D.Diag_id,
    DT.DeseaseType_Code,
    LpuP.Lpu_id,
    LpuP.Lpu_pid,
    null::int8 as DispClass_id,
    left(DConcid.Diag_Code, 3) ::varchar as DConcid_Code
  from dbo.EvnPL EPL
	left join params on 1=1
  left join dbo.Diag DConcid on DConcid.Diag_id = EPL.Diag_concid
	inner join dbo.EvnVizitPL as EVPLLast on EVPLLast.Evn_pid = EPL.Evn_id and EVPLLast.Evn_Index = EVPLLast.Evn_Count - 1 and (EVPLLast.Evn_deleted = 1 or EVPLLast.Evn_deleted is null)

	left join lateral
  (
	select --top 2
	  EDPLSop.Diag_id, EDPLSop.DeseaseType_id 
    from dbo.EvnDiagPLSop EDPLSop
    where 
      EDPLSop.Evn_pid = EVPLLast.Evn_id
	  and (EDPLSop.Evn_deleted = 1 or EDPLSop.Evn_deleted is null)
  ) Diags on true

  inner join dbo.Diag D on D.Diag_id = Diags.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  inner join dbo.v_DeseaseType DT on DT.DeseaseType_id = Diags.DeseaseType_id 
	inner join Lpu_W as LpuP on EPL.Lpu_id = LpuP.Lpu_id
  inner join v_PersonState as PS on PS.Person_id = EPL.Person_id
    and (p_EndAge > 3 or dbo.AgeYMD_pg(PS.Person_BirthDay, EPL.Evn_setDT, 1) between 0 and 2) --p_EndAge = 3 - это таблица 1500
	  and date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) between p_BegAge and p_EndAge
	  and (rpt.workage(PS.Person_BirthDay, PS.Sex_id, p_EndDate) = 1 and p_RepDiag_Part = 4000
	  or p_RepDiag_Part in (1000, 2000, 5000, 2, 1500, 1600, 3000))
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
  inner join LpuSection_W as LS on LS.LpuSection_id = EPL.LpuSection_id	
	where (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		and EPL.EvnClass_id IN (3,6) 
	  and EPL.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPL.EvnPLBase_IsFinish = 2
	  and (EPL.Evn_deleted = 1 or EPL.Evn_deleted is null)
	and EPL.Diag_id not between 10880 and 11502
	and EPL.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
),

d as
(
  select
    EPL_All.Diag_Code,
	COUNT(distinct EPL_all.cntValue) as v4, 
	
	COUNT(distinct case when DConcid_Code = 'W54' 
	  then EPL_all.cntValue end) as v4W54,

	COUNT(distinct case when p_RepDiag_Part = 1500
	  and EPL_all.AgeY = 0
	  then EPL_all.cntValue end) as v4_age0,

	COUNT(distinct case when p_RepDiag_Part = 1500
	  and EPL_all.AgeY = 0
	  and DConcid_Code = 'W54'
	  then EPL_all.cntValue end) as v4W54_age0,

	COUNT(distinct case when p_RepDiag_Part = 1500
	  and EPL_all.AgeY between 1 and 2
	  then EPL_all.cntValue end) as v4_age13,

	COUNT(distinct case when p_RepDiag_Part = 1500
	  and EPL_all.AgeY between 1 and 2
	  and DConcid_Code = 'W54'
	  then EPL_all.cntValue end) as v4W54_age13,
	
    COUNT(distinct case when p_RepDiag_Part = 1000 and EPL_all.AgeY between 0 and 4 
      or p_RepDiag_Part = 1500 and EPL_all.IsLessMonth = 2
      then EPL_all.cntValue end) as v5,
	
    COUNT(distinct case when (p_RepDiag_Part = 1000 and EPL_all.AgeY between 0 and 4 
        or p_RepDiag_Part = 1500 and EPL_all.IsLessMonth = 2)
      and DConcid_Code = 'W54'  
      then EPL_all.cntValue end) as v5W54,
	     
    COUNT(distinct case when EPL_all.AgeY between 5 and 9
      then EPL_all.cntValue end) as v6,
	     
    COUNT(distinct case when EPL_all.AgeY between 5 and 9
      and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v6W54,
	     
    COUNT(distinct case when EPL_all.Sex_id = 1
      then EPL_all.cntValue end) as v7,
	     
    COUNT(distinct case when EPL_all.Sex_id = 1
      and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v7W54,      
	  
    COUNT(distinct case when (PDisp_Open > 0 or PDisp_Close > 0)
      then EPL_all.Person_id end) as v8,      
	  
    COUNT(distinct case when (PDisp_Open > 0 or PDisp_Close > 0)
      and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v8W54,

    COUNT(distinct case when p_RepDiag_Part = 1500
	  and (PDisp_Open > 0 or PDisp_Close > 0)
	  and EPL_all.AgeY = 0
      then EPL_all.Person_id end) as v8_Age0,

    COUNT(distinct case when p_RepDiag_Part = 1500
	  and (PDisp_Open > 0 or PDisp_Close > 0)
	  and EPL_all.AgeY = 0
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v8W54_Age0,

    COUNT(distinct case when p_RepDiag_Part = 1500
	  and (PDisp_Open > 0 or PDisp_Close > 0)
	  and EPL_all.AgeY between 1 and 2
      then EPL_all.Person_id end) as v8_Age13,

    COUNT(distinct case when p_RepDiag_Part = 1500
	  and (PDisp_Open > 0 or PDisp_Close > 0)
	  and EPL_all.AgeY between 1 and 2
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v8W54_Age13,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2)
      then EPL_all.cntValue end) as v9,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2)
      and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v9W54,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2)
	  and EPL_all.AgeY = 0
      then EPL_all.cntValue end) as v9_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2)
	  and EPL_all.AgeY = 0
	  and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v9W54_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2)
	  and EPL_all.AgeY between 1 and 2
      then EPL_all.cntValue end) as v9_age13,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2)
	  and EPL_all.AgeY between 1 and 2
	  and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v9W54_age13,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
      then EPL_all.Person_id end) as v10,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
      and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v10W54,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
	  and EPL_all.AgeY = 0 
      then EPL_all.Person_id end) as v10_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
	  and EPL_all.AgeY = 0 
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v10W54_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
	  and EPL_all.AgeY between 1 and 2 
      then EPL_all.Person_id end) as v10_age13,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(DeseaseType_Code, 1) in (1,2) 
      and PDisp_Fir > 0
	  and EPL_all.AgeY between 1 and 2 
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v10W54_age13,
	  
    COUNT(distinct case when coalesce(EPL_all.DeseaseType_Code, 1) = 2  
	  and DispClass_id in (6,9,10,11,12, 5) 
      then EPL_all.cntValue end) as v11,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(EPL_all.DeseaseType_Code, 1) = 2  
	  and DispClass_id in (6,9,10,11,12, 5)
      and EPL_all.AgeY = 0 
      then EPL_all.cntValue end) as v11_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(EPL_all.DeseaseType_Code, 1) = 2  
	  and DispClass_id in (6,9,10,11,12, 5)
      and EPL_all.AgeY between 1 and 2
      then EPL_all.cntValue end) as v11_age13,
	  
    COUNT(distinct case when coalesce(EPL_all.DeseaseType_Code, 1) = 2  
	  and DispClass_id in (3,4,7,8, 1,2)
      then EPL_all.cntValue end) as v12,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) 
      and EPL_all.Sex_id = 1
      then EPL_all.cntValue end) as v13,
	  
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) 
      and EPL_all.Sex_id = 1
      and DConcid_Code = 'W54' 
      then EPL_all.cntValue end) as v13W54,
	
    COUNT(distinct case when coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
      then EPL_all.Person_id end) as v14,
	
    COUNT(distinct case when coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
      and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v14W54,
	
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
	  and EPL_all.AgeY = 0 
      then EPL_all.Person_id end) as v14_age0,
	
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
	  and EPL_all.AgeY = 0 
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v14W54_age0,
	
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
	  and EPL_all.AgeY between 1 and 2 
      then EPL_all.Person_id end) as v14_age13,
	
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and coalesce(PDisp_Open, 0) = 0 and PDisp_Close > 0
	  and EPL_all.AgeY between 1 and 2 
	  and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v14W54_age13,
	  
    COUNT(distinct case when PDisp_Open > 0
      then EPL_all.Person_id end) as v15,
	  
    COUNT(distinct case when PDisp_Open > 0
      and DConcid_Code = 'W54' 
      then EPL_all.Person_id end) as v15W54,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and PDisp_Open > 0
	  and EPL_all.AgeY = 0 
      then EPL_all.Person_id end) as v15_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and PDisp_Open > 0
	  and EPL_all.AgeY = 0 
	  and DConcid_Code = 'W54'
      then EPL_all.Person_id end) as v15W54_age0,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and PDisp_Open > 0
	  and EPL_all.AgeY between 1 and 2
      then EPL_all.Person_id end) as v15_age13,
	  
    COUNT(distinct case when p_RepDiag_Part = 1500
	  and PDisp_Open > 0
	  and EPL_all.AgeY between 1 and 2
	  and DConcid_Code = 'W54'
      then EPL_all.Person_id end) as v15W54_age13,
	  
    COUNT(distinct case when EPL_all.Sex_id = 1
	  and PDisp_Open > 0
      then EPL_all.Person_id end) as v16,
	  
    COUNT(distinct case when EPL_all.Sex_id = 1
	  and PDisp_Open > 0
	  and DConcid_Code = 'W54'
      then EPL_all.Person_id end) as v16W54	  

  from
  (
    select 
	  cntValue, Person_id, Sex_id, AgeY, IsLessMonth, 
	  Diag_Code, Diag_id, DispClass_id, Lpu_id, Lpu_pid, DeseaseType_Code, DConcid_Code 
	from EPL

    union all

	select 
	  cntValue, Person_id, Sex_id, AgeY, IsLessMonth, 
	  Diag_Code, Diag_id, DispClass_id, Lpu_id, Lpu_pid, DeseaseType_Code, '' ::varchar as DConcid_Code
	from EPLDisp
  ) EPL_all 
	left join params on 1=1
	  
  -- #131953 ищем карты Д-Учета
  left join lateral
  (
    select
	  COUNT(case when PersonDisp_begDate >= p_BegDate
	    and cast(coalesce(PersonDisp_DiagDate, PersonDisp_begDate) as date) between p_BegDate and p_EndDate
	    then PDisp.PersonDisp_id end) as PDisp_Fir,

	  COUNT(case when PersonDisp_endDate >= p_BegDate
	    and PersonDisp_endDate < p_EndDate + interval '1 day'
	    then PDisp.PersonDisp_id end) as PDisp_Close,

	  COUNT(case when PersonDisp_endDate >= p_EndDate + interval '1 day' 
	    or PersonDisp_endDate is null
	    then PDisp.PersonDisp_id end) as PDisp_Open
    
    from dbo.v_PersonDisp PDisp
	left join params on 1=1
    where
	  EPL_all.Person_id = PDisp.Person_id 
	  and PDisp.Diag_id = EPL_All.Diag_id
	  and PDisp.Lpu_id = EPL_all.Lpu_pid
	  and PersonDisp_begDate < p_EndDate + interval '1 day'
	  and (PersonDisp_endDate >= p_BegDate or PersonDisp_endDate is null) 
  ) PDisp on true

  group by EPL_All.Diag_Code
),

RD as
(
  select
  RD.RepDiag_Row,
	RD.RepDiag_Text1::text,
	RD.RepDiag_Text2::text,
	RD.RepDiag_Text3::text,
	rtrim(RD.RepDiag_FromDiag) ::varchar as RepDiag_FromDiag,
	rtrim(RD.RepDiag_ToDiag) ::varchar as RepDiag_ToDiag
  from rpt.v_RepDiag RD
	left join params on 1=1
  where
    RepDiag_SysNick = p_RepDiag_SysNick
    and RepDiag_Part = p_RepDiag_Part
)

select
  RD.RepDiag_Row ::integer as "RepDiag_Row",
  RD.RepDiag_Text1 ::text as "RepDiag_Text1",
  RD.RepDiag_Text2 ::text as "RepDiag_Text2",
  case when RD.RepDiag_Text3 = 'Р05-Р04' then 'P00-P04' else RD.RepDiag_Text3  end ::text as "RepDiag_Text3",-- по задаче PROMEDWEB-60054 нужно было вывести  код P00-P04 в пункт 17.0, хоть он и находится в диапозоне 2 000,очень странно, с аналитиком решили сделать пока что так (не задевая основного алгаритма). 
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v4W54 else d.v4 end), 0) ::integer as v4,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v4W54_age0 else d.v4_age0 end), 0) ::integer as v4_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v4W54_age13 else d.v4_age13 end), 0) ::integer as v4_age13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v5W54 else d.v5 end), 0) ::integer as v5,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v6W54 else d.v6 end), 0) ::integer as v6,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v7W54 else d.v7 end), 0) ::integer as v7,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v8W54 else d.v8 end), 0) ::integer as v8,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v8W54_age0 else d.v8_age0 end), 0) ::integer as v8_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v8W54_age13 else d.v8_age13 end), 0) ::integer as v8_age13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v9W54 else d.v9 end), 0) ::integer as v9,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v9W54_age0 else d.v9_age0 end), 0) ::integer as v9_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v9W54_age13 else d.v9_age13 end), 0) ::integer as v9_age13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v10W54 else d.v10 end), 0) ::integer as v10,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v10W54_age0 else d.v10_age0 end), 0) ::integer as v10_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v10W54_age13 else d.v10_age13 end), 0) ::integer as v10_age13,
  coalesce(SUM(d.v11), 0) ::integer as v11,
  coalesce(SUM(d.v11_age0), 0) ::integer as v11_age0,
  coalesce(SUM(d.v11_age13), 0) ::integer as v11_age13,
  coalesce(SUM(d.v12), 0) ::integer as v12,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v13W54 else d.v13 end), 0) ::integer as v13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v14W54 else d.v14 end), 0) ::integer as v14,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v14W54_age0 else d.v14_age0 end), 0) ::integer as v14_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v14W54_age13 else d.v14_age13 end), 0) ::integer as v14_age13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v15W54 else d.v15 end), 0) ::integer as v15,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v15W54_age0 else d.v15_age0 end), 0) ::integer as v15_age0,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v15W54_age13 else d.v15_age13 end), 0) ::integer as v15_age13,
  coalesce(SUM(case when RD.RepDiag_Text2 = '20.1' then d.v16W54 else d.v16 end), 0) ::integer as v16
from d
right join RD on d.Diag_Code between RD.RepDiag_FromDiag and RD.RepDiag_ToDiag --Diag_Code between RD.RepDiag_FromDiag and RD.RepDiag_ToDiag
group by
  RepDiag_Row,
  RD.RepDiag_Text1,
  RD.RepDiag_Text2,
  RD.RepDiag_Text3
order by RepDiag_Row
