Postgres Pro
Downloads
Home   >   mailing lists

Thread: Check Code Correction Current Period Prior Period Movement

Check Code Correction Current Period Prior Period Movement

From
Anthony Apollis
Date:
Please assist me?
I am trying to calculate 
 Current Period  Prior Period  Movement  
from my data, the date columns are:
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;
Attachment

Re: Check Code Correction Current Period Prior Period Movement

From
Adrian Klaver
Date:
On 11/23/23 06:02, Anthony Apollis wrote:
> Please assist me?
> I am trying to calculate
>   Current Period      Prior Period      Movement
> 
> from my data, the date columns are:
> 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.

Since you have not specified '...what it's supposed to do' is there is 
really not much that can be said. Your best bet is to create some sample 
input data and expected output and then create a test that verifies that 
the input passes through the view statements and ends up as the expected 
output.

-- 
Adrian Klaver
adrian.klaver@aklaver.com