Thread: Calculating Days/Time(Are Loops Neccessary?)
I have a table that needs to give me all data up until yesterday. This will be part of an SSIS package that runs monthly, a day before the 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);
On 9/19/23 10:09, Anthony Apollis wrote: > I have a table that needs to give me all data up until yesterday. This All data since, beginning of month, beginning of year, beginning of time? > will be part of an SSIS package that runs monthly, a day before the last > day. code is: You need to provide a simplified version of what you are trying to achieve. -- Adrian Klaver adrian.klaver@aklaver.com
On 2023-Sep-19, Anthony Apollis wrote:
> 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?
I didn't stop to understand your problem fully, but if you need the set
of calendar days in one month, you can use the generate_series()
function around some arithmetic that gives you the start of the month
plus when it ends, something like this:
with onedate(theday) as (values (date '2023-02-03'))
select g::date
from onedate,
generate_series(date_trunc('month', onedate.theday),
date_trunc('month', onedate.theday) + interval '1 month' - interval '1 day',
'1 day') g ;
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).