What the correct way of capturing difference between database queries?

user_3747564 0
Viewed 23

I'm not sure what I'm even asking here, so I will give you an example.

Every month I run a script that summarizes transactions for the previous month. It might look like this:

-- Beginning of period
SELECT SUM(cent_amount) FROM transactions WHERE date < '2022-01-01';
-- Sum of all transactions in the period
SELECT SUM(cent_amount) FROM transactions WHERE date >= '2022-01-01' AND date < '2022-02-01';

This works fine. But my problem is this: How would I handle someone inserting a new row in that table after I've pulled the report? If someone inserts a new row in January after I've run the report for March, I would need to add it as a "correction" in April. But how would I capture that "diff"? I'm not even sure what I want to google.

The data is stored in Postgres database

logic postgresql database
1 Answers

You can modify transactions table to store the date of the row insertion:

alter table transactions add column created_at timestamp default now();
update transactions set created_at = "date";
alter table transactions alter column created_at set not null;

Then you can filter only newly added records in subsequent reports:

SELECT SUM(cent_amount) FROM transactions 
  WHERE date >= '2022-01-01' AND date < '2022-02-01' -- this is from your query
  AND created_at >= '2022-04-01' -- only new rows since last report for April
;
user_14635950
Related