We are creating lagging date range reports (Month - 1 = previous months, Month - 2 = two months ago). For some reason, For the trailing month (Oct 1 - Oct 31), November 1 still populates in the output. The same happens for the previous month including Oct 1.
Any help here would be awesome!
SQL code: SELECT
"s_gold_layer_data"."transaction_ledger_materialized"."PARTNER_ID" AS "PARTNER_ID",
"s_gold_layer_data"."transaction_ledger_materialized"."INVOICE_NUMBER" AS "INVOICE_NUMBER",
"s_gold_layer_data"."transaction_ledger_materialized"."PAYMENT_AMOUNT" AS "PAYMENT_AMOUNT",
"s_gold_layer_data"."transaction_ledger_materialized"."TRANSACTION_TIMESTAMP" AS "TRANSACTION_TIMESTAMP"
FROM
"s_gold_layer_data"."transaction_ledger_materialized"
WHERE
(
CAST(
"s_gold_layer_data"."transaction_ledger_materialized"."TRANSACTION_TIMESTAMP" AS date
) + INTERVAL '1 month'
) BETWEEN DATE_TRUNC('month', (NOW() + INTERVAL '-1 month'))
AND DATE_TRUNC('month', NOW())
LIMIT
1048575
