menu_packs

WITH replacement_packs as(

SELECT
packages_menu."_id" AS pack_id
, packages_menu."Menu.Items.DishCode" as "dish_id"
, packages."Date" AS pack_date
, subscriptions."Settings.ProductCode" AS product_code
FROM packages_menu
LEFT JOIN packages ON packages_menu."_id" = packages."_id"
LEFT JOIN subscriptions ON subscriptions."_id" = packages."SubscriptionGuid"
where "Menu.Status" = 2
and packages."State" = 3
and packages."IsDeleted" is false
and packages."Date" >= '2022-01-01'
)

, packs as(
SELECT
packages."ClientGuid" AS client_id
, packages."_id" AS pack_id
, packages."Date" AS pack_date
, subscriptions."Settings.ProductCode" AS product_code
, "Meals.Containers._id" as "dish_id"
FROM packages
LEFT JOIN subscriptions ON subscriptions."_id" = packages."SubscriptionGuid"
LEFT JOIN menus ON packages."Date"= menus."Date" + '3 hour' AND subscriptions."Settings.ProductCode" = menus."Program"
WHERE packages."Date" >= '2022-01-01'
and packages."State" = 3
And packages."IsDeleted" is false
)

SELECT
packs.pack_id
, packs.pack_date
, case when packs.pack_id = replacement_packs.pack_id then replacement_packs.dish_id
       --when packs.pack_id != replacement_packs.pack_id then packs.dish_id
       else packs.dish_id
       end as "dish"
--, packs.dish_id
, packs.product_code
FROM packs
left join replacement_packs on packs."pack_id" = replacement_packs."pack_id"
GROUP BY packs.pack_id, packs.pack_date, "dish", packs.product_code
ORDER BY packs.pack_id