21 lines
1.0 KiB
Plaintext
21 lines
1.0 KiB
Plaintext
|
#!/bin/bash
|
||
|
|
|
||
|
|
|
||
|
|
echo -e " \n----------- Consignement Metabase Dashboard Code --------------\n"
|
||
|
|
|
||
|
|
echo "
|
||
|
|
--------------------------Consignement Business metrics--------------------------
|
||
|
|
|
||
|
|
drop table if exists OM_Order_item_Cogs_Promo_dump_Consignment
|
||
|
|
create table OM_Order_item_Cogs_Promo_dump_Consignment
|
||
|
|
as
|
||
|
|
select distinct transaction_date,brand_name,external_id as order_id,a.sku,quantity,cogs as "Cogs Per Unit Applied",(cogs*quantity) as "Total Cogs",Shipping_province,
|
||
|
|
case when b.cogs_promo notnull then 'Promo Cogs' when b.cogs_non_promo notnull then 'Non Promo Cogs' end as "Cogs Type"
|
||
|
|
from public.GM_dashboard a
|
||
|
|
left join public.sku_cogs_audit b on a.sku=b.sku and cast(a.transaction_date as date)=cast(b.created_at as date)
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/consignement/consignement_code.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/new_users/consignement/consignement_code.sql > consignement_code.log
|
||
|
|
|