WITH expertize AS (
SELECT l.id lid, e.procedure_id,
             MAX(CASE WHEN e.boss_id IS NOT NULL THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) boss,
             MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END) boss_date,
             MAX(CASE WHEN e.department_id = 10564 THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) komitet,
             MAX(CASE WHEN e.department_id = 10564 THEN e.expertize_date_plan ELSE NULL END) komitet_date,
             MAX(CASE WHEN e.department_id IN (10565,57725) THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) tsmets,
             MAX(CASE WHEN e.department_id IN (10565,57725) THEN e.expertize_date_plan ELSE NULL END) tsmets_date,
             MAX(control_date_End) AS control_date_End,
             MAX(expertize_date_End) AS expertize_date_End,
             MAX(expertize_date_plan) AS expertize_date_plan
  FROM po_procedure_expertize e
    LEFT JOIN lots l ON e.procedure_id = l.procedure_id
  JOIN users u ON COALESCE(e.boss_id,e.user_id) = u.id
     -- where e.procedure_id in (4270356,4182144,4205897)
 GROUP BY e.procedure_id, l.id
HAVING date_part('year', MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END)) IN (2022, 2023)
ORDER BY procedure_id
),
 nmck AS (
SELECT l.procedure_id,
SUM(start_price) AS nmсk
FROM procedures pr
JOIN lots l  On l.procedure_id = pr.id
GROUP BY l.procedure_id),
dates as(
SELECT
    pr.id,
    record_id,
     date date_all,
     --request_order_number,
    ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY date)  AS rating,
    Case
        when psfrom.step_id ='edit_request' And psto.step_id='wait_agreement_grbs_request' then 1
        when psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='edit_request' then 2
        when psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='wait_pkg_confirm_uo' then 3 else null end
        Alls,
  Case when psfrom.step_id ='edit_request' And psto.step_id='wait_agreement_grbs_request' then 1 else null end sent_to_grbs
    ,Case when psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='edit_request' then 2 else null end reject_grbs
    ,Case when psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='wait_pkg_confirm_uo' then 3 else null end soglas_grbs
        FROM (
        SELECT date,
                     CASE WHEN "from"='NULL' THEN null ELSE "from"::integer END "from",
                     CASE WHEN  "to"='NULL' THEN null ELSE "to"::integer END "to",
                     lh.record_id
        FROM lots_history lh
        WHERE lh.record_id IN (SELECT DISTINCT l.id
            FROM expertize e
            LEFT JOIN lots l ON l.procedure_id=e.procedure_id)
        AND lh.field = 'current_step'
        ORDER BY date
        ) a
        JOIN lots l on l.id = a.record_id
    JOIN procedures pr on l.procedure_id = pr.id

        LEFT JOIN procedure_steps psfrom on psfrom.id  = a.from
        LEFT JOIN procedure_steps psto on psto.id  = a.to
        Where  (psfrom.step_id ='edit_request' And psto.step_id='wait_agreement_grbs_request') OR (psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='wait_pkg_confirm_uo') OR
        (psfrom.step_id ='wait_agreement_grbs_request' And psto.step_id='edit_request')
        ORDER BY record_id, date)

Select

    d1.id procedure_id, -- ID_процедуры
    pp.request_order_number, -- Номер заявки на закупку
    vpt.name AS way, -- Способ определения поставщика
    pr.title AS purchase, -- Наименование закупки
  c_grbs.full_name AS grbsname, -- ГРБС
    c.full_name, -- заказчик
  c1.full_name AS customer, -- организатор
    vps.full_name lotstage, -- Статус
    n.nmсk, -- Н(М)ЦК, руб.
    d1.date_all date_sent, -- Дата, время "Отправлено на согласование в ГРБС"
    case when d2.reject_grbs is not null then d2.date_all else null end date_reject, --  Дата, время "Процедура отклонена в ГРБС"
    case when d2.soglas_grbs is not null then d2.date_all else null end date_soglas, --  Дата, время "Процедура отклонена в ГРБС"
    pr.regIstry_number, -- Номер извещени
    cc.contract_rnk-- РНК
From dates d1
join dates d2 on d1.rating+1 =d2.rating and d1.alls=1 and d1.id=d2.id
join procedures pr on d1.id=pr.id
JOIN po_procedures pp ON d1.id = pp.procedure_id
JOIN vocab_procedure_types vpt ON pr.procedure_type = vpt.id
JOIN lots l ON l.procedure_id = d1.id
JOIN lot_customers lc ON lc.lot_id = l.id AND lc.actual = TRUE
JOIN contragents c ON c.id = lc.customer_id
JOIN contragents c1 ON c1.id = pr.organizer_contragent_id
JOIN po_list_org_hierarchy ploh ON ploh.contragent_id = c.id AND ploh.actual = TRUE AND ploh.parent_type = 1
JOIN contragents c_grbs ON c_grbs.id = ploh.parent_contragent_id
JOIN procedure_steps ps ON ps.id = l.current_step
JOIN vocab_procedure_steps vps ON vps.pseudo::text = ps.step_id::text And vps.actual = true
LEFT JOIN nmck n ON n.procedure_id = pr.id
LEFT JOIN cm_contracts cc ON l.id = cc.lot_id
order by d1.id
--