Please assist me?
I am trying to calculate
Current Period Prior Period Movement from my data, the date columns
are:
[image: image.png]
Please check my code for any errors, code runs, i just want to make sure it
does what it's supposed to do.
-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View
-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";
CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"
AS
SELECT t."ZTBR_TransactionCode",
t."Company_Code",
t."Posting_Period", -- This is a period within a year
t."Fiscal_Year", -- This contains years 2019 to present
t."Profit_Center",
t."Account_Number",
t."Business_Process",
t."Internal_Order",
t."Amount_in_Company_Code_Currency",
t."Company_Code_Currency",
t."BRACS_FA",
t."Expense_Type",
t."Primary_ZTBR_TransactionCode",
t."Acct_Type",
t."BRACS_Level_1",
t."BRACS_Level_2",
t."BRACS_Level_3",
t."GCoA",
t."Account_Desc",
t."EXPENSE_FLAG",
t."BRACS",
t."BRACS_DESC",
t."Roll_Up_Currency",
t."Roll_Up_Account_Description",
t."BRACS_Account",
t."BRACS_Account_Description",
t."IS_BS",
t."Classification",
t."Roll_Up_Function",
t."Region",
t."Roll_Up",
t."Entity",
t."Entity_Name",
t."Entity_Level",
t."Entity_Level_1",
t."Entity_Level_2",
t."Entity_Level_3",
t."Entity_Level_4",
t."Entity_Level_5",
t."Entity_Level_6",
t."Region_Mapping_CoCd",
t."Region_Mapping_Sub_Region",
t."Region_Mapping_Region",
t."Region_Mapping_BRACS_Entity",
t."Region_Mapping_Consul",
t."Region_Mapping_Report",
t."Region_Mapping_Region_BRACS",
t."Region_Mapping_Group",
t."Region_Mapping_Group_BRACS",
t."DIM_BRACS_Account_Description",
t."DIM_Classification",
t."DIM_Direct_Primary_Key",
t."DIM_Order",
t."SDM_BRACSFA",
t."SDM_Function",
round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",
CASE
WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Current Period",
CASE
WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Prior Period",
CASE
WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"
WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN -
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Movement"
FROM model."IMETA_ZTRB_BRACS_Model_TA_BW3" t
LEFT JOIN ( SELECT "IMETA_Master_Currency_Data_TA_BR"."Currency",
"IMETA_Master_Currency_Data_TA_BR"."Currency name",
CASE
WHEN "IMETA_Master_Currency_Data_TA_BR"."Currency"::text =
'USD'::text THEN 1::double precision
ELSE "IMETA_Master_Currency_Data_TA_BR"."Currency to value"
END AS "Conversion rate"
FROM dim."IMETA_Master_Currency_Data_TA_BR"
WHERE "IMETA_Master_Currency_Data_TA_BR"."Scenario"::text =
'BUD'::text) curr ON t."Company_Code_Currency" = curr."Currency"::text;
ALTER TABLE model."IMETA_ZTRB_BRACS_Model_TA_BW_View"
OR
-- Drop the view if it exists to prevent errors
DROP VIEW IF EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW_View";
-- Create or replace the view
CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View" AS
SELECT
t."ZTBR_TransactionCode",
-- ... (other columns)
t."SDM_Function",
-- Convert the amount to USD
ROUND((t."Amount_in_Company_Code_Currency" / curr."Conversion
rate")::numeric, 2) AS "Amount in USD",
-- Calculate the Current Period Amount
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Current Period",
-- Calculate the Prior Period Amount
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Prior Period",
-- Calculate the Movement
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN -t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Movement"
FROM
model."IMETA_ZTRB_BRACS_Model_TA_BW3" t
LEFT JOIN
(SELECT
"Currency",
"Currency name",
CASE
WHEN "Currency" = 'USD' THEN 1
ELSE "Currency to value"
END AS "Conversion rate"
FROM dim."IMETA_Master_Currency_Data_TA_BR"
WHERE "Scenario" = 'BUD') curr
ON t."Company_Code_Currency" = curr."Currency";
-- Change the ownership of the view
ALTER VIEW
select * from model."IMETA_ZTRB_BRACS_Model_TA_BW_View" limit 500 OWNER TO
apollia;
Company_Code Posting_Period Fiscal_Year Profit_Center
Account_Number Business_Process Internal_Order
Amount_in_Company_Code_Currency
ZZA10 11 2019 ZAOPSPL 91021 -6648.47
ZZA10 6 2021 ZAOPSPL 51005020 5559.97
ZBA10 9 2022 PA061 91206 ZA08HAND61 163.6
ZCA10 2 2021 PA061 40103010 -14153
