Calculating Days/Time(Are Loops Neccessary?) - Mailing list pgsql-general

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);