final_foodcost

with dish_prices as (

-- цены на блюда без замен
SELECT DISTINCT
  m."Date" + interval '3 hour' as menu_date
, m."Meals.Containers._id" as dish_id
, m."Meals.Containers.Price"/10000 as dish_price
FROM menus m
WHERE m."Date">='01-11-2021'::timestamp

UNION

--цены на блюда, на которые была осуществлена замена (без учета ритейла)

SELECT DISTINCT
 packages."Date" as menu_date
,"public"."packages_menu"."Menu.Items.DishCode" as dish_id
,  menus."Meals.Containers.Price"/10000 as dish_price
FROM packages_menu
LEFT JOIN "public"."deliveryreports"
ON "public"."packages_menu"."Menu.Items.CookingDeliveryReportId"="public"."deliveryreports"."_id"
LEFT JOIN packages
ON packages_menu."_id"=packages."_id"
INNER JOIN menus ON "PackageGroups.FeedDate"= menus."Date" + '3 hour'
                AND "packages_menu"."Menu.Items.DishCode"=menus."Meals.Containers._id"
WHERE packages."IsDeleted" is false
and packages."Date">='01-01-2022'::timestamp
and packages."IsReplaced" is true
and packages."State"=3
and NOT "public"."packages_menu"."Menu.Items.DishCode" LIKE "public"."packages_menu"."Menu.Items.OriginalDishCode"
), packs as (

select
packages."_id"   as "pack_id"
, packages."Date" AS pack_date
, "packages"."DeliveryDate" AS delivery_date
, "SubscriptionSettings.ProductCode" as product_code
, COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id") as "dish_id"
-- , count(packages."_id") over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes_in_packs"
-- , count(COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id")) over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes"
, "SubscriptionSettings.Count" as "packs_count"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then true
       else false
       end as "replaced"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then "packages"."DeliveryReportGuid"
       else null
       end as "delivery_report_id"
from packages
left join packages_menu on (packages_menu."_id" = packages."_id"
                        and packages."IsReplaced" is true
                        )
left join menus ON (packages."Date"= menus."Date" + '3 hour'
                aND packages."SubscriptionSettings.ProductCode" = menus."Program"
                and packages."IsReplaced" is false
                and menus."IsDeleted" is false
                )
where packages."State" = 3
and packages."IsDeleted" is false
and packages."Date" >= '2022-01-01'
-- and packages."_id" = '00039e24-67cb-4dbd-b35c-634997077458'
order by packages."_id"

)

select
packs.*
, dish_prices.*

from packs
left join dish_prices on dish_prices."menu_date" = packs."pack_date"
                      and dish_prices."dish_id" = packs."dish_id"

where dish_prices.dish_price is null
Order by "pack_id"