CREATE TABLE ALL_srv (
    COD_ORG  SMALLINT NOT NULL,
    COD_POSL SMALLINT,
    COD_RIZN SMALLINT,
    data_e   DATE
);
CREATE TABLE all_TAR (
    MARK       varchar(15),
    COD_POSL   SMALLINT NOT NULL,
    COD_RIZN   SMALLINT NOT NULL,
    COST_UNIT  NUMERIC(10,5),
    COST_UN1   NUMERIC(10,5),
    NORM       NUMERIC(10,5)
);
commit;

INSERT into ALL_srv SELECT DISTINCT cod_org, 4, posl4, max(data_e) from SERVICE1 WHERE posl4<>0 group BY cod_org, posl4;
INSERT into ALL_srv SELECT DISTINCT cod_org, 8, posl8, max(data_e) from SERVICE1 WHERE posl8<>0 group BY cod_org, posl8;
commit;

INSERT INTO all_tar select case tr.MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM
    from TARIF1 tr where tr.data_e is NULL and tr.tarn=0 and tr.cod_posl=4 and tr.numb=1;
INSERT INTO all_tar select case tr.MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM
    from TARIF1 tr where tr.data_e is NULL and tr.tarn=0 and tr.cod_posl=8 and tr.numb=1;

CREATE INDEX IDX_ALL_srv ON ALL_srv (COD_ORG,COD_POSL,COD_rizn);
commit;

select
  tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM,
  list(distinct sr.COD_ORG,'; '),
  max(sr.DATA_E)
from ALL_TAR tr
LEFT JOIN all_srv sr on tr.COD_POSL=sr.COD_POSL and tr.COD_RIZN=sr.COD_RIZN
group BY tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM

WITH ALL_TAR as (
  select
    case MARK
      WHEN 0 then 'без ліч.'
      WHEN 1 then 'з ліч.'
      WHEN 2 then 'будинковий'
    end as lich,
    COD_POSL,
    COD_RIZN,
    COST_UNIT,
    COST_UN1,
    NORM
  from
    TARIF1
  where
    data_e is NULL and
    tarn=0 and
    cod_posl=4 and
    numb=1
  union all
  select
    case MARK
      WHEN 0 then 'без ліч.'
      WHEN 1 then 'з ліч.'
      WHEN 2 then 'будинковий'
    end as lich,
    COD_POSL,
    COD_RIZN,
    COST_UNIT,
    COST_UN1,
    NORM
  from
    TARIF1
  where
    data_e is NULL
    and tarn=0
    and cod_posl=8
    and numb=1)
with all_srv as (
  SELECT DISTINCT
    cod_org,
    4,
    posl4,
    max(data_e)
  from SERVICE1
  WHERE posl4<>0
  group BY cod_org, posl4
  union all
  SELECT DISTINCT
    cod_org,
    8,
    posl8,
    max(data_e)
  from SERVICE1
  WHERE posl8<>0
  group BY cod_org, posl8);

select
  tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM,
  list(distinct sr.COD_ORG,'; '),
  max(sr.DATA_E)
from ALL_TAR tr
LEFT JOIN all_srv sr on tr.COD_POSL=sr.COD_POSL and tr.COD_RIZN=sr.COD_RIZN
group BY tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM