!/bin/bash echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n" backDay=$1 echo $backDay echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n" reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d") echo 'reportDate'=$reportDate echo " drop table if exists public.sku_type_warehouse; create table public.sku_type_warehouse as select distinct sku , sku_type from public.final_basic_calucation_table; DROP TABLE IF EXISTS public.warehouse_analysis_stage1 ; CREATE TABLE public.warehouse_analysis_stage1 AS WITH basetable AS (SELECT A.id , CASE WHEN origin_area_id= '12416' THEN 'Cikarang' WHEN origin_area_id= '21599' THEN 'Semarang' WHEN origin_area_id= '30110' THEN 'Surabaya' WHEN origin_area_id= '64985' THEN 'Makassar' WHEN origin_area_id= '81092' THEN 'Medan' WHEN origin_area_id= '36700' THEN 'Samarinda' END origin_warehouse , destination_area_id , min(price) price , B.name partner_name, C.name Service_name FROM raena_transport_management.logistic_rate A LEFT JOIN raena_transport_management.logistic_partner B ON A.partner_id = B.id LEFT JOIN raena_transport_management.logistic_service C ON A.service_id = C.id WHERE A.active_status ='true' GROUP BY A.id , CASE WHEN origin_area_id= '12416' THEN 'Cikarang' WHEN origin_area_id= '21599' THEN 'Semarang' WHEN origin_area_id= '30110' THEN 'Surabaya' WHEN origin_area_id= '64985' THEN 'Makassar' WHEN origin_area_id= '81092' THEN 'Medan' WHEN origin_area_id= '36700' THEN 'Samarinda' END , destination_area_id , B.name , C.name) SELECT AA.*, BB.origin_warehouse expected_warehouse FROM (SELECT A.*, B.min_price FROM basetable A LEFT JOIN (SELECT destination_area_id , min(price) min_price, partner_name, Service_name FROM basetable GROUP BY destination_area_id , partner_name, Service_name) B ON A.destination_area_id=B.destination_area_id AND A.partner_name =B.partner_name AND A.Service_name= B.Service_name) AA LEFT JOIN (SELECT DISTINCT min_price, service_name, destination_area_id , origin_warehouse, row_number () over (partition BY min_price,service_name,destination_area_id ORDER BY min_price ,service_name ,destination_area_id) rnk FROM (SELECT DISTINCT A.destination_area_id, A.origin_warehouse , A.service_name, min_price --A.*,B.min_price FROM basetable A INNER JOIN (SELECT destination_area_id , min(price) min_price, partner_name, Service_name FROM basetable GROUP BY destination_area_id , partner_name, Service_name) B ON A.destination_area_id=B.destination_area_id AND A.partner_name =B.partner_name AND A.Service_name= B.Service_name AND min_price = Price)AA) BB ON AA.service_name = BB.service_name AND AA.min_price = BB.min_price AND AA.destination_area_id= BB.destination_area_id AND rnk = 1 ; DROP TABLE IF EXISTS public.warehouse_analysis_stage3; CREATE TABLE public.warehouse_analysis_stage3 AS SELECT DISTINCT coalesce(B.order_id) order_id , A.reference_id, E.name service_name , CASE WHEN warehouse_code='WH-CGK45' THEN 'Cikarang' WHEN warehouse_code='WH-SRG18' THEN 'Semarang' WHEN warehouse_code='WH-SUB51' THEN 'Surabaya' WHEN warehouse_code='WH-UPG04' THEN 'Makassar' WHEN warehouse_code='WH-MES07' THEN 'Medan' WHEN warehouse_code='WH-AAP02' THEN 'Samarinda' END actual_warehouse, B.rate_id, sku, C.quantity quantity, B.shipping_amount shipping_amount, C.payment_amount*C.Quantity payment_amount FROM raena_transport_management.logistic_order A LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.reference_id = B.id LEFT JOIN raena_transport_management.logistic_service E ON A.service_id = E.id LEFT JOIN (SELECT AA.order_id , AA.sales_sub_order_shipment_id, AA.sku, CC.Quantity , CC.discounted_price payment_amount FROM (SELECT order_id , sales_sub_order_shipment_id, coalesce(BB.sku ,parent_sku) sku, sum(coalesce(BB.quantity , AA.quantity)) Quantity FROM raena_order_management.sales_sub_order AA LEFT JOIN raena_order_management.sales_sub_order_parent_child BB ON AA.id = BB.sales_sub_order_id GROUP BY 1, 2, 3) AA LEFT JOIN (SELECT DISTINCT external_id , product_sku , quantity, discounted_price FROM base_netsuite_final) CC ON AA.order_id = CC.external_id AND AA.sku = CC.product_sku ) C ON A.reference_id = C.sales_sub_order_shipment_id WHERE cast(A.created_at AS date)>='2022-04-01' AND A.status IN ('RETURNED', 'DELIVERED', 'PICKED_UP', 'CREATED'); DROP TABLE IF EXISTS public.warehouse_analysis_stage3_final_v1; CREATE TABLE public.warehouse_analysis_stage3_final_v1 AS SELECT A.reference_id AS Order_id , reference_id , service_name , 'Cikarang' actual_warehouse , -1 rate_id , product_sku sku , B.quantity , B.shipping_cost shipping_amount , B.discounted_price*B.quantity payment_amount FROM public.warehouse_analysis_stage3 A LEFT JOIN base_netsuite_final B ON A.reference_id = B.external_id WHERE A.order_id IS NULL UNION SELECT * FROM public.warehouse_analysis_stage3 WHERE order_id IS NOT NULL; DROP TABLE IF EXISTS public.warehouse_analysis_stage3_final; CREATE TABLE public.warehouse_analysis_stage3_final AS SELECT * FROM public.warehouse_analysis_stage3_final_v1 WHERE order_id NOT IN (SELECT DISTINCT id FROM raena_order_management.order WHERE is_campaign='true') AND payment_amount IS NOT NULL UNION SELECT A.Order_id, reference_id, service_name, actual_warehouse, rate_id, A.sku , A.quantity, shipping_amount, B.payment_price FROM public.warehouse_analysis_stage3_final_v1 A INNER JOIN (SELECT order_id , sales_sub_order_shipment_id, coalesce(BB.sku ,parent_sku) sku, sum(coalesce(BB.quantity , AA.quantity)) Quantity, sum(coalesce(BB.payment_amount , AA.payment_amount)) payment_price FROM raena_order_management.sales_sub_order AA LEFT JOIN raena_order_management.sales_sub_order_parent_child BB ON AA.id = BB.sales_sub_order_id WHERE order_id IN (SELECT DISTINCT id FROM raena_order_management.order WHERE is_campaign='true') GROUP BY 1, 2, 3) B ON A.order_id = B.order_id AND A.reference_id= B.sales_sub_order_shipment_id AND A.sku = B.sku; DROP TABLE IF EXISTS public.warehouse_analysis_stage1_1; CREATE TABLE public.warehouse_analysis_stage1_1 AS SELECT DISTINCT Z.id order_id , A.reference_id, service_name , actual_warehouse, A.rate_id, A.sku, A.quantity, A.shipping_amount, A.payment_amount, payment_status , Z.status order_status, Z.order_placed_by, Z.created_at, z.is_campaign, Z.created_by FROM raena_order_management.order Z INNER JOIN public.warehouse_analysis_stage3_final A ON Z.id=A.order_id WHERE Z.payment_status ='Paid' AND cast(Z.created_at AS date)>='2022-04-01'; DROP TABLE IF EXISTS public.warehouse_analysis_stage1_2; CREATE TABLE public.warehouse_analysis_stage1_2 AS SELECT DISTINCT Z.id order_id , BB.sku sku, BB.quantity quantity, BB.shipping_cost shipping_amount, BB.Brand_name AS Brand_name , shipping_to order_type, BB.discounted_price*BB.quantity payment_amount, payment_status , Z.status order_status, Z.order_placed_by, Z.created_at, z.is_campaign, Z.created_by FROM raena_order_management.order Z INNER JOIN public.gm_dashboard BB ON Z.id = BB.external_id WHERE Z.payment_status ='Paid' AND cast(Z.created_at AS date)>='2022-04-01' AND BB.external_id NOT IN (SELECT order_id FROM public.warehouse_analysis_stage3_final) ; DROP TABLE IF EXISTS public.warehouse_analysis_stage1_3_v1; CREATE TABLE public.warehouse_analysis_stage1_3_v1 AS SELECT Z.id AS order_id , A.reference_id, A.service_name , A.actual_warehouse, A.rate_id, coalesce(A.sku,B.sku) sku, coalesce(A.quantity,B.quantity) quantity, coalesce(A.shipping_amount,B.shipping_amount) shipping_amount, Z.shipping_to order_type, coalesce(A.payment_amount,B.payment_amount) payment_amount, Z.payment_status , Z.status order_status, Z.order_placed_by, Z.created_at, z.is_campaign, Z.created_by FROM raena_order_management.order Z LEFT JOIN public.warehouse_analysis_stage1_1 A ON Z.id = A.order_id LEFT JOIN public.warehouse_analysis_stage1_2 B ON Z.id = B.order_id WHERE Z.payment_status ='Paid' AND cast(Z.created_at AS date)>='2022-04-01'; DROP TABLE IF EXISTS public.warehouse_analysis_stage1_3; CREATE TABLE public.warehouse_analysis_stage1_3 AS SELECT order_id, A.reference_id, service_name, actual_warehouse, rate_id, sku, quantity, case when b.shipping_amount is not null then ((b.shipping_amount)/(b.order_weight))*(b.final_weight) else A.shipping_amount end shipping_amount, order_type, payment_amount, A.payment_status, order_status, order_placed_by, created_at, is_campaign, created_by FROM public.warehouse_analysis_stage1_3_v1 A LEFT JOIN OM_Logistic_final_shipping B ON A.reference_id= B.reference_id; DROP TABLE IF EXISTS public.warehouse_analysis_stage2_v1; CREATE TABLE public.warehouse_analysis_stage2_v1 AS SELECT A.*, B.brand_name, CASE WHEN (B.Final_weight*A.Quantity)<1.3 THEN 1 WHEN (B.Final_weight*A.Quantity)>=1.3 AND (ABS((B.Final_weight*A.Quantity)) - FLOOR(ABS((B.Final_weight*A.Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((B.Final_weight*A.Quantity))+1 ELSE FLOOR((B.Final_weight*A.Quantity)) END AS final_weight FROM public.warehouse_analysis_stage1_3 A LEFT JOIN (SELECT sku , B.name brand_name, CASE WHEN height*width*LENGTH/6000 > weight THEN height*width*LENGTH/6000 ELSE weight END Final_weight, height*width*LENGTH/6000 vw, weight FROM raena_catalog_management.product A LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id) B ON A.sku = B.sku; DROP TABLE IF EXISTS public.warehouse_analysis_stage2; CREATE TABLE public.warehouse_analysis_stage2 AS SELECT *, (final_weight*shipping_amount)/sum(final_weight) over(partition BY reference_id) new_shipment_amount FROM public.warehouse_analysis_stage2_v1; select count(1) from public.warehouse_analysis_stage2 ; DROP TABLE IF EXISTS public.shipping_fee_coupon_purpose; CREATE TABLE public.shipping_fee_coupon_purpose AS SELECT O.order_id , C.sub_order_id, payment_status, order_status, sub_order_status, sub_order_shipment_status, tierName, payment_amount, shipping_amount AS shipping_fee, applied_shipping_amount AS applied_shipping_fee, shipping_to order_type, order_placed_by, email, mobile, CASE WHEN D.min_date = O.Created_at AND O.reseller_id = D.reseller_id THEN 'New' ELSE 'Old' END User_type , province, created_at FROM (SELECT id AS order_id , reseller_id, json_extract_path_text(A.reseller_info,'tierName',TRUE) tierName, json_extract_path_text(A.reseller_info,'email',TRUE) email, json_extract_path_text(A.reseller_info,'mobile',TRUE) mobile, lower(shipping_province) province, payment_status, status order_status, shipping_to , order_placed_by , created_at FROM raena_order_management.order A WHERE is_archived = 'false' AND cast(created_at AS date)>='2022-04-01' ORDER BY created_at DESC) O LEFT JOIN (SELECT id sub_order_shipping_id, order_id, shipping_amount, applied_shipping_amount, status sub_order_shipment_status FROM raena_order_management.sales_sub_order_shipment ORDER BY 2) B ON O.order_id = B.order_id LEFT JOIN (SELECT id sub_order_id , order_id, A.status sub_order_status, sales_sub_order_shipment_id , B.discounted_price*B.quantity payment_amount FROM raena_order_management.sales_sub_order A INNER JOIN public.business_report B ON A.order_id = B.external_id AND A.parent_sku = B.sku ORDER BY 2, 4 DESC) C ON B.sub_order_shipping_id= C.sales_sub_order_shipment_id LEFT JOIN (SELECT reseller_id, min(created_at) min_date FROM raena_order_management. ORDER WHERE payment_status= 'Paid' AND is_archived = 'false' GROUP BY reseller_id) D ON O.reseller_id = D.reseller_id ORDER BY o.created_at DESC; select count(1) from public.shipping_fee_coupon_purpose; DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse ; CREATE TABLE public.rate_sheet_wise_expected_warehouse AS SELECT order_id , reference_id Sub_order_shipping_id, sku , case when actual_warehouse is null then 'Cikarang' else actual_warehouse end actual_warehouse , case when expected_warehouse is null then 'Cikarang' else expected_warehouse end expected_warehouse, CASE WHEN actual_warehouse <>expected_warehouse THEN 'Yes' ELSE 'No' END AS missed_Inventory, service_name , order_type, payment_amount, payment_status , order_status, order_placed_by, am_name, am_email, sum(quantity)quantity, sum(new_shipment_amount) shipping_amount, count(order_id) number_of_order, brand_name, tierName, email, mobile , province , User_type, is_campaign, cast((created_at+interval'7 Hours') AS date) AS created_date, DSF_flag, brand_type FROM ( SELECT DISTINCT A.order_id , reference_id, A.service_name , actual_warehouse, rate_id, A.sku, quantity, new_shipment_amount, expected_warehouse, coalesce(Y.Name,A.Brand_name) AS Brand_name , order_type, payment_amount, payment_status , order_status, order_placed_by, AA.name am_name, AA.email am_email, tierName, DD.email, mobile , province , User_type, is_campaign, A.created_at, case when A.order_id like '%DSF%' then 'Yes' Else 'No' end DSF_flag, CASE WHEN coalesce(Y.Name,A.Brand_name) IN ('LUXCRIME', 'SKINTIFIC', 'TRUEVE', 'SANIYE', 'BEAUDELAB', 'BRASOV', 'FACE REPUBLIC', 'SKIN1004', 'PREMIERE BEAUTE', 'ALLURA', 'LIPLAPIN', 'ROUNDLAB', 'FACE FLUX', 'DOLLGORAE', 'SKINUA', 'PUREFORET', 'SKINTIFIC', 'OHMYSKIN', 'FEAT FOR SKIN', 'SECONDATE', 'KYND', 'PURNAMA', 'BASE', 'LAVIE LASH', 'REI SKIN', 'USTRAA', 'BRUNBRUN PARISGLOWINC', 'SOONHAN', 'THE YEON', 'MIXSOON', 'KOSE COSMEPORT') THEN 'High GM' WHEN coalesce(Y.Name,A.Brand_name) IN ('W DRESSROOM', 'BEAUSTA', 'Dewycel', 'GLUTANEX', 'HISTOIRE NATURELLE', 'FORENCOS', 'BELLFLOWER', 'MAXCLINIC') THEN 'EL/PL' END brand_type FROM public.warehouse_analysis_stage2 A LEFT JOIN (SELECT DISTINCT order_id , tierName, email, mobile , province , User_type FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar) LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id AND A.service_name = D.service_name)AA WHERE sku IS NOT NULL GROUP BY order_id , reference_id, sku , case when actual_warehouse is null then 'Cikarang' else actual_warehouse end , case when expected_warehouse is null then 'Cikarang' else expected_warehouse end , CASE WHEN actual_warehouse <>expected_warehouse THEN 'Yes' ELSE 'No' END , service_name , order_type, payment_amount, payment_status , order_status, order_placed_by, am_name, am_email, brand_name, tierName, email, mobile , province , User_type, is_campaign, cast((created_at+interval'7 Hours') AS date), DSF_flag, brand_type; update public.rate_sheet_wise_expected_warehouse set payment_amount =0 from public.gm_dashboard where discounted_price= 0 and public.rate_sheet_wise_expected_warehouse.order_id = public.gm_dashboard.external_id and public.rate_sheet_wise_expected_warehouse.sku = public.gm_dashboard.sku ; select count(1) from public.rate_sheet_wise_expected_warehouse ; DROP TABLE IF EXISTS quantity_trend_new; CREATE TABLE quantity_trend_new AS SELECT is_campaign , frequency , transaction_date , child_sku , warehouse , quantity FROM quantity_trend_v1 WHERE transaction_date <'2022-04-01' UNION SELECT is_campaign , 'Month', date_trunc('Month',created_date) :: date , sku , actual_warehouse, sum(quantity) FROM public.rate_sheet_wise_expected_warehouse GROUP BY 1, 2, 3, 4, 5; DROP TABLE IF EXISTS quantity_trend; CREATE TABLE quantity_trend AS SELECT * FROM quantity_trend_new; ; DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse_expected_shipping; CREATE TABLE public.rate_sheet_wise_expected_warehouse_expected_shipping AS SELECT order_id , reference_id Sub_order_shipping_id, sku , case when actual_warehouse is null then 'Cikarang' else actual_warehouse end actual_warehouse , case when expected_warehouse is null then 'Cikarang' else expected_warehouse end expected_warehouse, CASE WHEN actual_warehouse <>expected_warehouse THEN 'Yes' ELSE 'No' END AS missed_Inventory, service_name , order_type, payment_amount, payment_status , order_status, order_placed_by, am_name, am_email, sum(quantity)quantity, sum(new_shipment_amount) shipping_amount, sum(price) Per_kg_cost, count(order_id) number_of_order, brand_name, tierName, email, mobile , province , User_type, cast(created_at AS date) AS created_date FROM (SELECT DISTINCT A.order_id , reference_id, A.service_name , actual_warehouse, rate_id, A.sku, quantity, new_shipment_amount, expected_warehouse, coalesce(Y.Name,A.Brand_name) AS Brand_name , order_type, payment_amount, D.price, payment_status , order_status, order_placed_by, AA.name am_name, AA.email am_email, tierName, DD.email, mobile , province , User_type, A.created_at FROM public.warehouse_analysis_stage2 A LEFT JOIN (SELECT DISTINCT order_id , tierName, email, mobile , province , User_type FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar) LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id AND A.service_name = D.service_name)AA WHERE sku IS NOT NULL GROUP BY order_id , reference_id, sku , case when actual_warehouse is null then 'Cikarang' else actual_warehouse end , case when expected_warehouse is null then 'Cikarang' else expected_warehouse end , CASE WHEN actual_warehouse <>expected_warehouse THEN 'Yes' ELSE 'No' END , service_name , order_type, payment_amount, payment_status , order_status, order_placed_by, am_name, am_email, brand_name, tierName, email, mobile , province , User_type, cast(created_at AS date); select count(1) from public.rate_sheet_wise_expected_warehouse_expected_shipping ; DROP TABLE IF EXISTS om_distr_actaul_origin; CREATE TABLE om_distr_actaul_origin AS SELECT DISTINCT sub_order_shipping_id, service_name, actual_warehouse, expected_warehouse, order_weight, B.partner_id, B.service_id, rate_id, destination_area_id, origin_area_id AS actual_origin, CASE WHEN expected_warehouse='Cikarang' THEN 12416 WHEN expected_warehouse='Semarang' THEN 21599 WHEN expected_warehouse='Surabaya' THEN 30110 WHEN expected_warehouse='Makassar' THEN 64985 WHEN expected_warehouse='Medan' THEN 81092 WHEN expected_warehouse='Samarinda' THEN 36700 END AS expected_origin FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a INNER JOIN raena_transport_management.logistic_order b ON a.sub_order_shipping_id=b.reference_id LEFT JOIN raena_transport_management.logistic_rate c ON b.rate_id=c.id; DROP TABLE IF EXISTS om_distr_actaul_origin_2; CREATE TABLE om_distr_actaul_origin_2 AS SELECT DISTINCT sub_order_shipping_id, actual_warehouse, expected_warehouse, c.price, c.eta_min FROM om_distr_actaul_origin b INNER JOIN raena_transport_management.logistic_rate c ON b.expected_origin=c.origin_area_id AND b.destination_area_id=c.destination_area_id and b.service_id = c.service_id and b.partner_id = c.partner_id INNER JOIN raena_transport_management.logistic_service s ON s.id=c.service_id AND s.name=b.service_name WHERE rate_id NOTNULL AND actual_warehouse!=expected_warehouse; DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse; CREATE TABLE public.OM_Logistic_base_exepected_warehouse AS SELECT DISTINCT cast(lo.created_at AS date) AS created_at, reference_id, order_weight, lo.shipping_amount, rate_id, lr.price AS Shipping_fee_per_kg_weight, lr.eta_min AS expected_eta_min FROM raena_transport_management.logistic_order lo INNER JOIN om_distr_actaul_origin_2 lr ON lo.reference_id=lr.sub_order_shipping_id WHERE lo.created_at >= '2022-04-01' AND rate_id IS NOT NULL; --order_id for suborders DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_2; CREATE TABLE public.OM_Logistic_base_exepected_warehouse_2 AS SELECT created_at, reference_id, order_weight, shipping_amount, rate_id, shipping_fee_per_kg_weight, expected_eta_min, coalesce(order_id,reference_id) AS order_id, sku, payment_amount, sales_sub_order_id, quantity FROM (SELECT a.*, b.order_id, b.parent_sku AS sku, b.payment_amount , b.id AS sales_sub_order_id, b.quantity FROM public.OM_Logistic_base_exepected_warehouse a LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id) A; ---sku tagging DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_3; CREATE TABLE public.OM_Logistic_base_exepected_warehouse_3 AS SELECT DISTINCT a.*, coalesce(b.sku,a.sku) AS new_sku, coalesce(b.quantity,a.quantity) AS new_quantity FROM public.OM_Logistic_base_exepected_warehouse_2 a LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id; DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_4; CREATE TABLE public.OM_Logistic_base_exepected_warehouse_4 AS SELECT Created_at, order_id, reference_id, sales_sub_order_id, new_quantity AS quantity, order_weight, shipping_amount, shipping_fee_per_kg_weight, expected_eta_min, new_sku, payment_amount, b.weight AS actual_weight, b.volume_weight FROM public.OM_Logistic_base_exepected_warehouse_3 a LEFT JOIN (SELECT sku, id, weight, (height*width*LENGTH)/6000 AS volume_weight FROM raena_catalog_management.product) b ON a.new_sku=b.sku WHERE reference_id LIKE 'SH%' ORDER BY 2; --final dataset DROP TABLE IF EXISTS public.OM_Logistic_final_shipping_exepected_warehouse_base; CREATE TABLE public.OM_Logistic_final_shipping_exepected_warehouse_base AS SELECT Created_at, order_id, reference_id, paymnet_amount, shipping_amount, Shipping_fee_per_kg_weight, expected_eta_min, CASE WHEN order_weight<1.3 THEN 1 WHEN order_weight>=1.3 AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1 ELSE FLOOR(order_weight) END AS order_weight, CASE WHEN compared_weight<1.3 THEN 1 WHEN compared_weight>=1.3 AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1 ELSE FLOOR(compared_weight) END AS final_weight FROM (SELECT *, CASE WHEN volume_weight>actual_weight THEN volume_weight WHEN volume_weight= '2022-04-01' AND lo.rate_id IS NOT NULL; DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_6; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_6 AS SELECT created_at, reference_id, order_weight, shipping_amount, actual__JTR_min_eta, actual_JTR_per_kg_price, expected__JTR_min_eta, expected_JTR_per_kg_price, coalesce(order_id,reference_id) AS order_id, sku, payment_amount, sales_sub_order_id, quantity FROM (SELECT a.*, b.order_id, b.parent_sku AS sku, b.payment_amount , b.id AS sales_sub_order_id, b.quantity FROM public.om_shipment_jtr_shipping_fee_base_5 a LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id); ---sku tagging DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_7; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_7 AS SELECT DISTINCT a.*, coalesce(b.sku,a.sku) AS new_sku, coalesce(b.quantity,a.quantity) AS new_quantity FROM public.om_shipment_jtr_shipping_fee_base_6 a LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id; DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_8; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_8 AS SELECT Created_at, order_id, reference_id, sales_sub_order_id, new_quantity AS quantity, order_weight, shipping_amount, actual__JTR_min_eta, actual_JTR_per_kg_price, expected__JTR_min_eta, expected_JTR_per_kg_price, new_sku, payment_amount, b.weight AS actual_weight, b.volume_weight FROM public.om_shipment_jtr_shipping_fee_base_7 a LEFT JOIN (SELECT sku, id, weight, (height*width*LENGTH)/6000 AS volume_weight FROM raena_catalog_management.product) b ON a.new_sku=b.sku WHERE reference_id LIKE 'SH%' ORDER BY 2; --final dataset DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_9; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_9 AS SELECT Created_at, order_id, reference_id, paymnet_amount, shipping_amount, actual__JTR_min_eta, actual_JTR_per_kg_price, expected__JTR_min_eta, expected_JTR_per_kg_price, CASE WHEN order_weight<1.3 THEN 1 WHEN order_weight>=1.3 AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1 ELSE FLOOR(order_weight) END AS order_weight, CASE WHEN compared_weight<1.3 THEN 1 WHEN compared_weight>=1.3 AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1 ELSE FLOOR(compared_weight) END AS final_weight FROM (SELECT *, CASE WHEN volume_weight>actual_weight THEN volume_weight WHEN volume_weight=10; DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_11; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_11 AS SELECT DISTINCT a.Sub_order_shipping_id, (b.actual_JTR_shipping_fee+insurance_fee) AS actual_JTR_shipping_fee, actual__JTR_min_eta, (b.expected_JTR_shipping_fee+insurance_fee) AS expected_JTR_shipping_fee, expected__JTR_min_eta FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a LEFT JOIN public.om_shipment_jtr_shipping_fee_base_10 b ON a.Sub_order_shipping_id=b.reference_id INNER JOIN raena_transport_management.logistic_order o ON a.Sub_order_shipping_id=o.reference_id and o.status <>'CANCELLED' WHERE Sub_order_shipping_id IS NOT NULL; DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_12; CREATE TABLE public.om_shipment_jtr_shipping_fee_base_12 AS SELECT b.*, a.expected_eta_min FROM public.rate_sheet_wise_expected_warehouse_expected_shipping_final a INNER JOIN public.om_shipment_jtr_shipping_fee_base_11 b ON a.Sub_order_shipping_id=b.Sub_order_shipping_id AND expected__JTR_min_eta-a.expected_eta_min<=3; drop table if exists public.rate_sheet_wise_expected_warehouse_destination_area_id; CREATE TABLE public.rate_sheet_wise_expected_warehouse_destination_area_id AS select distinct order_id , reference_id Sub_order_shipping_id, destination_area_id FROM (SELECT DISTINCT A.order_id , reference_id, destination_area_id FROM public.warehouse_analysis_stage2 A LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id AND A.service_name = D.service_name WHERE sku IS NOT null )AA; " > /home/ec2-user/cronjob/warehouseAnalysis/warehouseAnalysis.sql psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f/home/ec2-user/cronjob/warehouseAnalysis/warehouseAnalysis.sql