Create Temp Table expertise
(
    lid int4 PRIMARY KEY,
    procedure_id int4,
    boss varchar(200),
    boss_date date,
    komitet varchar(200),
    komitet_date date,
    tsmets varchar(200),
    tsmets_date date,
    control_date_End date,
    expertize_date_End date,
    expertize_date_plan date
)
;

-- заполняем времнную таблицу eshop_rej
Insert into expertise
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
 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;


-- создаем временную таблицу cons_lot выводим те лоты по которым больше 1 заказчика т.е. совместные
Create Temp Table hist AS (
SELECT record_id,
             COUNT(date) AS count_date_uo
 FROM (SELECT record_id, date
                  FROM po_procedure_hIstory
           WHERE field = 'sed_registration_date' AND record_id IN (SELECT procedure_id FROM expertise)) a
                 GROUP BY record_id);

Create Temp Table  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);



Create Temp Table date_c AS (
SELECT pr.id, record_id,
               date date_all,
                     ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY date)  AS rating,
                     pr.date_notice_published,
                     case when date::date<=coalesce(pr.date_notice_published::date, now()) then 1 else 0 end date_publ_sr,
                     psto.step_id,
                     case when date_part('year', date::date) < 2023 and psto.step_id = 'assign_expert_uo' then 1
                          when date_part('year', date::date) >= 2023 and psto.step_id = 'wait_expert_uo' then 1
                                when psto.step_id = 'recall_request_review' then 1
                                when psto.step_id = 'expertize_complete' then 1
                                else 0 end step_priznak
        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 expertise e
            LEFT JOIN lots l ON l.procedure_id=e.procedure_id)
        AND lh.field = 'current_step'
        ORDER BY date
        ) a
        LEFT JOIN procedure_steps psfrom on psfrom.id  = a.from
        LEFT JOIN procedure_steps psto on psto.id  = a.to
        JOIN lots l on l.id = a.record_id
    JOIN procedures pr on l.procedure_id = pr.id
        WHERE (psto.step_id = 'expertize_complete' OR
                    psto.step_id = 'assign_expert_uo' OR
                    psto.step_id = 'recall_request_review' OR
                    psto.step_id = 'wait_expert_uo')
                    AND
                    (case when date_part('year', date::date) < 2023 and psto.step_id = 'assign_expert_uo' then 1
                          when date_part('year', date::date) >= 2023 and psto.step_id = 'wait_expert_uo' then 1
                                when psto.step_id = 'recall_request_review' then 1
                                when psto.step_id = 'expertize_complete' then 1
                                else 0 end) = 1
        ORDER BY record_id, date);

select * from date_c where id = 4178069 limit 123

Select
    a.* ,
    RANK() OVER(ORDER BY a.step_id,a.rating) rnk,
    ROW_NUMBER() over ( partition by a.step_id ) rn
FROM date_c a
LEFT JOIN (select record_id, date_all, rating-1 rating, step_id from date_c) b ON a.record_id = b.record_id AND a.rating = b.rating
JOIN lots l on l.id = a.record_id
JOIN procedures pr on l.procedure_id = pr.id
where a.id=4311985
Order by a.rating