From 6a62da21effb394c0ab2f1eb59133afa8ccb05e7 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Mon, 9 Oct 2023 09:19:47 +0700 Subject: if qty available greater than zero then include in pricelist --- calculate_price_after_disc.ktr | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/calculate_price_after_disc.ktr b/calculate_price_after_disc.ktr index 80046e5..5a11b82 100644 --- a/calculate_price_after_disc.ktr +++ b/calculate_price_after_disc.ktr @@ -619,7 +619,8 @@ dw bi indoteknik (localhost) select p.product_id, p.item_code, p.name, b.pricing_group, -coalesce((select +coalesce(( + select case when human_last_update is null then coalesce(system_price, 0) when system_last_update is null then coalesce(product_price, 0) @@ -628,8 +629,9 @@ coalesce((select else coalesce(product_price, 0) end as purchase_price from purchase_pricelist_dw ppd + join product p2 on p2.product_id = ppd.product_id where ppd.product_id = p.product_id - and (system_last_update >= (now() - '365 days'::interval) or human_last_update >= (now() - '365 days'::interval)) + and (system_last_update >= (now() - '365 days'::interval) or human_last_update >= (now() - '365 days'::interval) or p2.qty_available > 0) order by ppd.count_trx_po desc, ppd.count_trx_po_vendor desc limit 1 ),0) as purchase_price, -- cgit v1.2.3