Scribd
Upload
25 views

Duplicate Wor

The document drops and creates several temporary tables. It copies data from S3 buckets into the temporary tables. It then performs several insert and update statements to populate and transform data in the temporary tables. Finally, it inserts transformed data from the temporary tables into the final temp_daily_sales table.

Uploaded by

Gnan Shetty
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
25 views

Duplicate Wor

The document drops and creates several temporary tables. It copies data from S3 buckets into the temporary tables. It then performs several insert and update statements to populate and transform data in the temporary tables. Finally, it inserts transformed data from the temporary tables into the final temp_daily_sales table.

Uploaded by

Gnan Shetty
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

drop table temp_stg_inter_retailtransaction;

drop table temp_src_mystore_cashsheet_taxcalculationsproductsales;


drop table temp_src_mystore_cashsheet_taxcalculationsnonproductsales;
drop table temp_src_mystore_cashsheet;
drop table temp_daily_sales;

create table temp_stg_inter_retailtransaction as (select * from


stg_inter_retailtransaction where 1=2);
create table temp_src_mystore_cashsheet_taxcalculationsproductsales as (select *
from src_mystore_cashsheet_taxcalculationsproductsales where 1=2);
create table temp_src_mystore_cashsheet_taxcalculationsnonproductsales as (select *
from src_mystore_cashsheet_taxcalculationsnonproductsales where 1=2);
create table temp_src_mystore_cashsheet as (select * from src_mystore_cashsheet
where 1=2);

copy temp_src_mystore_cashsheet_taxcalculationsproductsales from


's3://riccbidatadev/my_store_extract/83043/20220315/TaxCalculationsProductSales.csv
'
credentials'aws_access_key_id=AKIAINYE3CA4OCG2TRYA;aws_secret_access_key=fCpE0Y6pR2
rAYxJQY1gTw+qqm0sWiPmn20ui6HIV'
delimiter ',' acceptinvchars removequotes DATEFORMAT 'auto' NULL AS 'NULL'
FILLRECORD;

copy temp_src_mystore_cashsheet_taxcalculationsnonproductsales from


's3://riccbidatadev/my_store_extract/83043/20220315/TaxCalculationsNonProductSales.
csv'
credentials'aws_access_key_id=AKIAINYE3CA4OCG2TRYA;aws_secret_access_key=fCpE0Y6pR2
rAYxJQY1gTw+qqm0sWiPmn20ui6HIV'
delimiter ',' acceptinvchars removequotes DATEFORMAT 'auto' NULL AS 'NULL'
FILLRECORD;

copy temp_src_mystore_cashsheet from


's3://riccbidatadev/my_store_extract/83043/20220315/CashSheet.csv'
credentials'aws_access_key_id=AKIAINYE3CA4OCG2TRYA;aws_secret_access_key=fCpE0Y6pR2
rAYxJQY1gTw+qqm0sWiPmn20ui6HIV'
delimiter ',' acceptinvchars removequotes DATEFORMAT 'auto' NULL AS 'NULL'
FILLRECORD;

insert into temp_stg_inter_retailtransaction


(store_no,reference_application,businessday,net_sales_amount_product)
select distinct store_no,'MY-STORE',cast(businessday as date) as
businessday,totalrecalc
from temp_src_mystore_cashsheet_taxcalculationsproductsales where namerecalc =
'Saudi Riyadh 15% TAX rec.'
group by store_no,businessday,totalrecalc;

UPDATE temp_stg_inter_retailtransaction a
set net_sales_amount_non_product = (SELECT totalrecalc AS
net_sales_amount_non_product
from temp_src_mystore_cashsheet_taxcalculationsnonproductsales b
where
a.store_no = b.store_no and a.businessday = b.businessday and b.namerecalc = 'Saudi
Riyadh 15% TAX rec.'
GROUP BY store_no,businessday,totalrecalc) where 1=1;

update temp_stg_inter_retailtransaction c set


gc = t.ntotaldaygc,
eat_in_tac = t.oi_eatinquantity,
eat_in_sales_br = t.oi_eatinamount,
take_out_tac = t.oi_takeoutquantity,
take_out_sales_br = t.oi_takeoutamount,
drive_through_gc = t.oi_mcdrivequantity,
drive_through_sales = t.oi_mcdriveamount,
breakfast_tac = t.oi_breakfastquantity,
breakfast_sales = t.oi_breakfastamount,
mccafe_tac = t.oi_coffcorquantity,
mccafe_sales = t.oi_coffcoramount
FROM temp_src_mystore_cashsheet t
where c.businessday = t.businessday and c.store_no = t.store_no;

update temp_stg_inter_retailtransaction set store_number = b.store_no_tld,


latitude = b.latitude,
longitude = b.longitude
from store_master b
where temp_stg_inter_retailtransaction.store_no = b.store_no_short;

update temp_daily_sales c set


net_sales_amount_product = t.net_sales_amount_product,
net_sales_amount_non_product = t.net_sales_amount_non_product,
gc = t.gc,
eat_in_tac = t.eat_in_tac,
eat_in_sales_br = t.eat_in_sales_br,
take_out_tac = t.take_out_tac,
take_out_sales_br = t.take_out_sales_br,
drive_through_gc = t.drive_through_gc,
drive_through_sales = t.drive_through_sales,
breakfast_tac = t.breakfast_tac,
breakfast_sales = t.breakfast_sales,
mccafe_tac = t.mccafe_tac,
mccafe_sales = t.mccafe_sales,
store_number = t.store_number,
latitude = t.latitude,
longitude = t.longitude
from temp_stg_inter_retailtransaction t
where c.businessday = t.businessday and c.store_no = t.store_no;

INSERT INTO temp_daily_sales (store_no, reference_application, businessday,


net_sales_amount_product, net_sales_amount_non_product, gc, eat_in_tac,
eat_in_sales_br, take_out_tac, take_out_sales_br, drive_through_gc,
drive_through_sales, breakfast_tac,
breakfast_sales,mccafe_tac,mccafe_sales,store_number,latitude,longitude)
(
SELECT distinct t.store_no, t.reference_application, t.businessday,
t.net_sales_amount_product, t.net_sales_amount_non_product, t.gc,t.eat_in_tac,
t.eat_in_sales_br, t.take_out_tac, t.take_out_sales_br, t.drive_through_gc,
t.drive_through_sales, t.breakfast_tac,
t.breakfast_sales,t.mccafe_tac,t.mccafe_sales,t.store_number,t.latitude,t.longitude
from temp_stg_inter_retailtransaction t
LEFT JOIN
temp_daily_sales c ON
t.businessday = c.businessday and t.store_no = c.store_no
WHERE
c.businessday IS NULL and c.store_no IS NULL
group by t.store_no, t.reference_application, t.businessday,
t.net_sales_amount_product, t.net_sales_amount_non_product, t.gc,t.eat_in_tac,
t.eat_in_sales_br, t.take_out_tac, t.take_out_sales_br, t.drive_through_gc,
t.drive_through_sales, t.breakfast_tac,
t.breakfast_sales,t.mccafe_tac,t.mccafe_sales,t.store_number,t.latitude,t.longitude
);

You might also like