create table tmp.mesolduslugacomplex_010222 AS
select
    *, '{}'::jsonb AS error
from (
select distinct   
    cx.uslugacomplex_id,
		UPPER(nullif(TRIM(g.service_code),'')) AS serv_code,
    mes.mes_id,
	g.csg_code AS mes_code,
    nullif(g.gender_code,'')::bigint as sex_id,
    case g.age_group_code when '1' then 4 when '2' then 9 when '3' then 10 when '4' then 11 when '5' then 5 when '6' then 6 when '7' then 15 end as mesagegroup_id,
    diag.diag_id,
		UPPER(nullif(TRIM(g.diagnosis_code),'')) AS diag_code,
    diag_n.diag_id as diag_nid,
		UPPER(nullif(TRIM(g.alt_diagnosis_code),'')) AS diag_ncode,
    diag_o.diag_id as diag_oid,
		UPPER(nullif(TRIM(g.comp_diagnosis_code),'')) AS diag_ocode,
    case when coalesce(diag.diag_id,diag_n.diag_id,diag_o.diag_id) is not null then 2 end as mesolduslugacomplex_isdiag,
    g.from_dt as mesolduslugacomplex_begdt,  
    case g.duration_code when '2' then 4 when '3' then 11 when '4' then 21 end as mesolduslugacomplex_duration,
    case g.duration_code when '1' then 3 when '2' then 10 when '3' then 20 when '4' then 30 end as mesolduslugacomplex_durationto,
    dts.drugtherapyscheme_id,
    case when g.criteria_code ~ '^rb\d{1}$' then replace(g.criteria_code,'rb','')::bigint end as rehabscale_id,
    case when g.criteria_code = 'it1' then case when g.age_group_code = '5' then 4 else 5 end end as mesolduslugacomplex_sofascalepoints,
    case g.criteria_code when 'it1' then 72 when 'it2' then 480 end as mesolduslugacomplex_ivlhours,
    case when g.criteria_code = 'rbs' then (select uslugacomplex_id from v_uslugacomplex cxa where cxa.uslugacomplex_code = 'B05.069.005' and cxa.uslugacomplex_enddt is null) end as uslugacomplex_aid,
    case when g.criteria_code = 'rbs' then (select uslugacomplex_id from v_uslugacomplex cxb where cxb.uslugacomplex_code = 'B05.069.006' and cxb.uslugacomplex_enddt is null) end as uslugacomplex_bid,
    case when g.fr_code ~ '^fr' then substr(g.fr_code,3,2)::bigint end as mesolduslugacomplex_fracfrom,
    case when g.fr_code ~ '^fr' then right(g.fr_code,2)::bigint end as mesolduslugacomplex_fracto,
    md.mesdop_id
from tmp.csg_group_data_010222 g
join dbo.v_mesold mes on UPPER(TRIM(g.csg_code)) = UPPER(TRIM(mes.mesold_num))
left join dbo.v_uslugacomplex cx on UPPER(nullif(TRIM(g.service_code),'')) = UPPER(nullif(TRIM(cx.uslugacomplex_code),'')) and cx.uslugacomplex_enddt is null
   
 left join dbo.v_diag diag on
    case
        when UPPER(nullif(TRIM(g.diagnosis_code),'')) is null then false
        when UPPER(TRIM(g.diagnosis_code)) = 'C00-C80' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'C00' and 'C80'
        when UPPER(TRIM(g.diagnosis_code)) = 'C81-C96' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'C81' and 'C96'
        when UPPER(TRIM(g.diagnosis_code)) = 'D00-D09' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'D00' and 'D09'
        when UPPER(TRIM(g.diagnosis_code)) = 'D45-D47' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'D45' and 'D47'
        when UPPER(TRIM(g.diagnosis_code)) = 'Q20-Q28' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'Q20' and 'Q28'
        when UPPER(TRIM(g.diagnosis_code)) = 'C.' then diag.diaglevel_id = 4 and left(diag.diag_code,1) = 'C'
        when UPPER(TRIM(g.diagnosis_code)) = 'I.' then diag.diaglevel_id = 4 and left(diag.diag_code,1) = 'I'       
        else (
                diag.diaglevel_id = 4 and (UPPER(TRIM(g.diagnosis_code)) = diag.diag_code or UPPER(TRIM(g.diagnosis_code)) = diag.diag_code||'.')
        )
    end
    and (diag.diag_enddate is null or diag.diag_enddate>g.from_dt)
   
 left join dbo.v_diag diag_n on
    case
        when UPPER(nullif(TRIM(g.alt_diagnosis_code),'')) is null then false
        when UPPER(TRIM(g.alt_diagnosis_code)) = 'C.' then diag_n.diaglevel_id = 4 and left(diag_n.diag_code,1) = 'C'
        else (
            diag_n.diaglevel_id = 4 and (UPPER(TRIM(g.alt_diagnosis_code)) = diag_n.diag_code or UPPER(TRIM(g.alt_diagnosis_code)) = diag_n.diag_code||'.')
        )
    end
    and diag_n.diag_enddate is null
      
 left join dbo.v_diag diag_o on
    case
        when UPPER(nullif(TRIM(g.comp_diagnosis_code),'')) is null then false
        when exists (select 1 from v_diag d where d.diag_code = UPPER(TRIM(g.comp_diagnosis_code)) and d.diag_enddate is null and d.diaglevel_id = 3) then diag_o.diaglevel_id = 4 and left(UPPER(TRIM(g.comp_diagnosis_code)),3) = left(diag_o.diag_code,3)
        else (
            diag_o.diaglevel_id = 4 and (UPPER(TRIM(g.comp_diagnosis_code)) = diag_o.diag_code or UPPER(TRIM(g.comp_diagnosis_code)) = diag_o.diag_code||'.')
        )
    end
    and diag_o.diag_enddate is null
   
 left join dbo.v_drugtherapyscheme dts on dts.drugtherapyscheme_code = TRIM(g.criteria_code) and dts.drugtherapyscheme_enddate is null
 left join dbo.v_mesdop md on md.mesdop_code = TRIM(g.criteria_code) and md.region_id = (select getregion()) and md.mesdop_enddt is null
where
    mes.mes_enddt is null
    and mes.mesold_num <> 'st29.007' --политравма не расчитываетсяпо группировщику
     
    --не берем диагнозы третьего уровня
    and case when length(UPPER(TRIM(g.diagnosis_code))) = 3 then exists (select 1 from v_diag d where UPPER(TRIM(g.diagnosis_code)) = d.diag_scode and d.diag_enddate is null and d.diaglevel_id = 4) else true end
    and case when length(UPPER(TRIM(g.alt_diagnosis_code))) = 3 then exists (select 1 from v_diag d where UPPER(TRIM(g.alt_diagnosis_code)) = d.diag_scode and d.diag_enddate is null and d.diaglevel_id = 4) else true end
    and case when length(UPPER(TRIM(g.comp_diagnosis_code))) = 3 then exists (select 1 from v_diag d where UPPER(TRIM(g.comp_diagnosis_code)) = d.diag_scode and d.diag_enddate is null and d.diaglevel_id = 4) else true end
     
    ----выполнять блоками, запрос тормозит из за отсутствия индекса по code в drugtherapyscheme
    --g.id between 1 and 4999
    --g.id between 5000 and 9999
    --g.id between 10000 and 14999
    --g.id between 15000 and 19999
    --g.id between 20000 and 24999
    --g.id between 25000 and 29999
    --g.id between 30000 and 32499
    --g.id between 32500 and 34999
    --g.id between 35000 and 39999
    --g.id between 40000 and 44999
    --g.id between 45000 and 49999
    --g.id between 50000 and 54999
    --g.id >= 55000
)t
;

