The data transformation process involves updating the year in the TRANSACTIONS table.
The SQL commands provided in the document demonstrate how to convert specific years to new values. For example, the command UPDATE TRANSACTIONS SET DATE = DATEADD(YEAR,1,DATE) WHERE YEAR(DATE) = 2021; converts all transactions from the year 2021 to 2022.
Additionally, the document addresses the issue of null values in the BANK column. The SQL command UPDATE TRANSACTIONS SET Bank = CASE WHEN YEAR(DATE) = 2018 THEN 'Southern Bank' WHEN YEAR(DATE) = 2019 THEN 'Northern Bank' WHEN YEAR(DATE) = 2020 THEN 'Central Bank' WHEN YEAR(DATE) = 2021 THEN 'DBS Bank' WHEN YEAR(DATE) = 2022 THEN 'Sky Bank' ELSE Bank END WHERE Bank IS NULL; updates the null values with corresponding bank names based on the transaction year.
Furthermore, the document includes instructions for adding a new column, "Age," to the CLIENT table. The command ALTER TABLE CLIENT ADD COLUMN Age INT; adds the new column, and the subsequent command UPDATE CLIENT SET AGE = DATEDIFF('YEAR',BIRTH_DATE,'2022-12-19'); populates the column with the age of each client based on their birth date.
Overall, the document provides a comprehensive guide for data transformation in the banking system, covering various aspects such as updating years, handling null values, adding columns, and updating data in tables.
In a data transformation , the following changes were made to the 'TXN_YEAR'
These changes reflect a shift in the transaction year values, updating them to the specified target years.
--DATA TRANSFORMATION
/*
CONVERT 2021 TXN_YEAR TO 2022
CONVERT 2020 TXN_YEAR TO 2021
CONVERT 2018 TXN_YEAR TO 2020
CONVERT 2017 TXN_YEAR TO 2019
CONVERT 2016 TXN_YEAR TO 2018
WE HAVE TO CHANGE YEAR IN THESE TABLES
➡️CARD
➡️LOAN
➡️TRANSACTIONS
*/
-- CHECKING YEAR IN AACCOUNT TABLE
SELECT DISTINCT YEAR(DATE) FROM ACCOUNT;
-- CHECKING YEAR IN TRANSACTIONS TABLE
SELECT DISTINCT YEAR(DATE) FROM TRANSACTIONS;
-- CHECKING YEAR IN CARD TABLE
SELECT DISTINCT YEAR(ISSUED) FROM CARD;
-- CHECKING YEAR IN LOAN TABLE
SELECT DISTINCT YEAR(DATE) FROM LOAN;
-- UPDATING YEAR IN TRANSACTIONS TABLE
-- CONVERT 2021 TXN_YEAR TO 2022
UPDATE TRANSACTIONS
SET DATE = DATEADD(YEAR,1,DATE) WHERE YEAR(DATE) = 2021;