select z.*
,ce.Parent_Category,
ce.cancelled_per,
ce.paymode,
re.Parent_Category,
re.returned_per,
rtoe.Parent_Category,
rtoe.rtoed_per
from
(
select order_id,cast(y.paymode as char) paymode,date(success_date) as dated,x.product_size_id,
Product_name,product_status,child_category_name,
parent_category_name from
(
select
product_size_id,
size,
Product_Id,
Product_name,
product_price,
mrp,
all_offer_price,
product_status,
product_launched_at,
base_inventory,
ptype,
stock_factor,
sales_factor,
member_price,
product_offer_type_id,
product_offer_type,
child_category_id,
parent_category_id,
category_gender,
child_category_name,
parent_category_name,
sleeve,
neck,
subtype,
cat_type,
subclass,
brand,
model,
x_factor,
y_factor,
cogs,
category_status,
design_id,
design_name,
design_status,
design_created_at,
design_updated_at,
designer_name,
color_name,
parent_color_name,
child_color_name,
stock_quantity,
product_size_qty_available
from product
)
x
right join
(
select
order_id,
product_size_id,
parent_id,
payment_gateway_enum,
payment_gateway_name,
order_status_enum,
order_status_name,
device_type,
shipping_money,
cod_money,
case when is_cod=0 then 'prepaid' else 'cod' end as paymode,
user_id,
success_date,
order_product_price,
offer_type,
offer_type_enum,
is_loyalty_order,
original_price,
city,
state,
hra_classification,
net_paid,
NonPromotionalWallet_debit_amount,
PromotionalWallet_debit_amount,
order_deductions_amount,
packing_date,
shipping_date,
delivered_date,
rto_date,
rto_initiated_date,
date(success_date) as dated
from order
where order_id=28644521 -- success_date >= '2019-08-01'
)
y
on x.product_size_id=y.product_size_id
)z
left join cancelled ce
on ce.date=z.dated and ce.Parent_Category=z.parent_category_name and ce.paymode = z.paymode
left join return re
on re.date=z.dated and re.Parent_Category=z.parent_category_name
left join rto rtoe
on rtoe.date=z.dated and rtoe.Parent_Category=z.parent_category_name and rtoe.paymode = z.paymode
;
