DROP TABLE IF EXISTS tmp_cur_forecast ;
CREATE TEMPORARY TABLE tmp_cur_forecast AS
--определяем самый актуальный прогноз на каждую неделю
WITH gf_prev AS (
SELECT f.code_lm::text AS item,
    f.site_code  AS loc,
    f.estm_forecast AS forecast,
    f.calculation_date ,
    concat(f."year", f.week_num)::INT AS sem,
    ROW_NUMBER() OVER(partition BY f.code_lm, f.site_code, concat(f."year", f.week_num) ORDER BY f.calculation_date DESC) AS rn
    FROM replenishment_marts.v_gold_forecast f
    WHERE f.site_code = 13
    ),
--отбираем самый актуальный прогноз для недели
gf AS (SELECT * FROM gf_prev WHERE rn = 1)
SELECT gf.item, gf.loc, gf.forecast, sem.semddeb, sem.semdfin
FROM gf
JOIN gold_refgwr_ods.v_fctsem sem ON gf.sem = sem.semnsem
;

--первые чеки (я тут уже просто взяла артикулы, проданные за два дня в октябре, чтобы найти хоть какой-то прогноз по ним
WITH new_i AS (
SELECT DISTINCT a.store_id, a.item, a.opened_dttm::DATE
    FROM (
    SELECT rl.store_id, rl.line_item_id::text AS item, rl.opened_dttm::DATE,
        ROW_NUMBER() OVER(partition BY rl.store_id, rl.line_item_id ORDER BY rl.opened_dttm ASC) rn
    FROM dds.v_receipt_lines_public rl
    WHERE rl.store_id=13
    ) a
    WHERE --a.rn = 1 and
    a.opened_dttm::DATE BETWEEN '2022-10-01'::DATE
    -- - interval '42 days'
    AND '2022-10-02'::DATE
    )
SELECT new_i.store_id, new_i.item, tcf.item AS item_f, --new_i.opened_dttm,
tcf.forecast,
CASE WHEN forecast notnull THEN 'есть прогноз'
ELSE 'нет прогноза' END AS ff
FROM new_i
LEFT JOIN tmp_cur_forecast tcf ON TRIM(tcf.item) = TRIM(new_i.item) AND
tcf.loc = new_i.store_id --and tcf.semdfin opened_dttm
ORDER BY 3 DESC