September 19, 2023 at 10:07 am
I have a table that need to give me all data up until yesterday. This will be part of an SSIS package that runs monthly, a day before last day. code is:
-- Create the new table if it doesn't exist and insert data
CREATE TABLE IF NOT EXISTS "model"."IMETA_ZTBR_BRACS_Model_TA_BW" AS
SELECT
fact."ZTBR_TransactionCode",
fact."Company_Code",
fact."Posting_Period",
fact."Fiscal_Year",
fact."Profit_Center",
fact."Account_Number",
fact."Business_Process",
fact."Internal_Order",
fact."Amount_in_Company_Code_Currency",
fact."Company_Code_Currency",
fact."BRACS_FA",
fact."Expense_Type",
fact."BRACS_ACCT_Key",
fact."CC_Direct",
fact."Segment_PC",
fact."CC_Master_FA",
fact."Region_Secondary_Key",
fact."Direct_Indirect_Secondary_Key",
fact."Source_Description_Secondary_Key",
fact."Entity_Secondary_Key",
fact."Master_BRACS_Secondary_Key",
bracs_map."Acct Type",
bracs_map."Level 1" AS "BRACS_Level1",
bracs_map."Level 2" AS "BRACS_Level2",
bracs_map."Level 3" AS "BRACS_Level3",
bracs_map."GCoA",
bracs_map."Account Desc",
bracs_map."EXPENSE FLAG",
bracs_map."BRACS",
bracs_map."BRACS_DESC",
bracs_map."Primary_ZTBR_TransactionCode",
direct_indirect_map."BRACS Account Description",
direct_indirect_map."CLASSIFICATION",
direct_indirect_map."Direct_Primary_Key",
region_map."CoCd",
region_map."Region",
region_map."Sub Region",
region_map."BRACS Entity",
region_map."Consul",
region_map."Report",
region_map."Region BRACS",
region_map."Group",
region_map."Group BRACS",
region_map."Region_Primary_Key",
entity_map."Entity",
entity_map."Entity Name",
entity_map."Entity Level",
entity_map."Level 1" AS "Entity_Level1",
entity_map."Level 2" AS "Entity_Level2",
entity_map."Level 3" AS "Entity_Level3",
entity_map."Level 4" AS "Entity_Level4",
entity_map."Level 5" AS "Entity_Level5",
entity_map."Level 6" AS "Entity_Level6",
entity_map."Entity_ID",
src_desc_map."BRACS_Key",
src_desc_map."BRACSFA",
src_desc_map."Function"
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
LEFT JOIN
dim."IMETA_BRACS_Mapping" AS bracs_map
ON
fact."Account_Number"::text = bracs_map."GCoA"::text AND
fact."Expense_Type"::text = bracs_map."EXPENSE FLAG"::text
LEFT JOIN
dim."IMETA_Direct_Indirect_Mapping_New" AS direct_indirect_map
ON
bracs_map."Account Desc" = direct_indirect_map."BRACS Account Description"
LEFT JOIN
dim."IMETA_Region_Mapping" AS region_map
ON
fact."Company_Code"::text = region_map."CoCd"::text
LEFT JOIN
dim."IMETA_Entity_Mapping" AS entity_map
ON
region_map."BRACS Entity" = entity_map."Entity"
LEFT JOIN
dim."IMETA_Source_Description_Mapping" AS src_desc_map
ON
ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"
WHERE
src_desc_map."BRACSFA" IS NOT NULL;
I have inherited this code, problem is it is over code, i believe. The package is gonna run once a month and this code run is a loop. How can this loop be running and checking data up until last day, if it only run once a month?
NUMBER OF LOOPS FOR POSTGRESQL ETL:
SELECT
CASE
WHEN (((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)) <= 30 THEN 1
ELSE
CEIL(((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)/30) --30 DAY INTERVALS UNLESS LESS
END
AS "Number of days"
START DATE SCRIPT FOR POSTGRESQL
SELECT
REPLACE(
CAST(
CAST(
CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
END AS DATE
) AS TEXT),'-','') AS "Start Date"
END DATE SCRIPT OF LOOP FOR POSTGRESQL
SELECT
REPLACE(
CAST(
CAST(
CASE
WHEN
(CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
END) = '2020-07-01'
THEN
'2020-07-31'
WHEN (CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
END) + INTERVAL '30 day' < CAST(NOW() AS DATE) THEN (CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER)) + interval '1 day'
END) + INTERVAL '30 day'
ELSE
CAST(NOW() - INTERVAL '1 day' AS DATE)
END AS DATE
) AS TEXT),'-','') AS "End date"
The business requirement and code seem to be conflicting, am i right?
If the package runs only once a month will this code then suffice?
SELECT MIN("ZTBR_TransactionCode") AS min_transaction_code,
MAX("ZTBR_TransactionCode") AS max_transaction_code
select count(*) FROM system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";
INSERT INTO "model"."IMETA_ZTBR_BRACS_Model_TA_BW" (
"ZTBR_TransactionCode",
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Internal_Order",
"Amount_in_Company_Code_Currency",
"Company_Code_Currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Region_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key",
"Entity_Secondary_Key",
"Master_BRACS_Secondary_Key",
"Loaddate"
)
SELECT
"ZTBR_TransactionCode",
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Internal_Order",
"Amount_in_Company_Code_Currency",
"Company_Code_Currency",
"BRACS_FA",
"Expense_Type",
"BRACS_ACCT_Key",
"CC_Direct",
"Segment_PC",
"CC_Master_FA",
"Region_Secondary_Key",
"Direct_Indirect_Secondary_Key",
"Source_Description_Secondary_Key",
"Entity_Secondary_Key",
"Master_BRACS_Secondary_Key",
current_timestamp AS "Loaddate" -- Populate Loaddate with the current timestamp
FROM system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
WHERE DATE_TRUNC('day', "Loaddate") = DATE_TRUNC('day', current_timestamp);
I am using POSTGRES, i believe the principle stays the same. I welcome input/help, i can just translate the code in Postgres
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy