USE expenses_db_azure;
GO

---------------------------------------------------------------
-- Create an sp that copies amex01 and cleans it

DROP PROCEDURE IF EXISTS sp_expense_02_clean_amex;
GO

CREATE PROCEDURE sp_expense_02_clean_amex 

AS

PRINT 'AMEX CLEAN - START INSERT'
INSERT INTO stage02_amex_clean 
SELECT * FROM stage01_amex;

PRINT 'AMEX CLEAN - START DELETE'
  
--get rid of amex payments
DELETE FROM stage02_amex_clean WHERE exp_desc LIKE '%PAYMENT RECEIVED%';
PRINT 'AMEX CLEAN - END'

GO

--------------------------------------------------------------
-- Create an sp that copies amex01 and cleans it


DROP PROCEDURE IF EXISTS sp_expense_02_clean_mast;
GO

CREATE PROCEDURE sp_expense_02_clean_mast 

AS

PRINT 'MAST CLEAN - START INSERT'
INSERT INTO stage02_mast_clean 
SELECT * FROM stage01_mast;
PRINT 'MAST CLEAN - END'
GO

---------------------------------------------------------------
-- Create an sp that copies amex01 and cleans it

DROP PROCEDURE IF EXISTS sp_expense_02_clean_lloyds;
GO

CREATE PROCEDURE sp_expense_02_clean_lloyds 

AS

PRINT 'LLOYDS CLEAN - START INSERT'
INSERT INTO stage02_lloyds_clean 
SELECT * FROM stage01_lloyds;

PRINT 'LLOYDS CLEAN - START DELETE'
  
--get rid of amex payments
DELETE FROM stage02_lloyds_clean WHERE exp_amount IS NULL;
PRINT 'LLOYDS CLEAN - END'

GO