I have a table that is updated every day where new data is inserted/appended underneath the previous day's data. I want to find the difference in data between the 2 dates.

The idea is to only return the last line I have in the table, which is the only line of data that is not a copy from the previous day.

I wrote the code here:

SELECT * FROM table1 t1
LEFT JOIN table1 t2 ON (t1.id_number = t2.id_number)
WHERE t1.id_number IS NULL

This code returns no values, it's just empty. Does that mean I have no differences in the records on those 2 days or does the code not work? I'm not sure how to verify. Also, new records are appended every day to the same table, so I only want the difference between the current date and the day before. Would I write something like:

"WHERE daily_date is CURDATE() and daily_date is CURDATE()-1" or something like that?

CodePudding user response：

I'm not overly familiar whether you can join a table to itself, but can't you just query with the date filter set to the past two days?

Something like below;

SELECT * FROM table1
WHERE date_daily BETWEEN GETDATE()-1 AND GETDATE()
ORDER BY account_number

CodePudding user response：

You used id_number instead of account_number, which is why no data is returned. Additionally, you need to specify which dates are t1 and t2.

Something like this:

SELECT * FROM table1 t1
LEFT JOIN table1 t2 
ON t1.account_number = t2.account_number
AND t1.daily_date = '12-27-2022'
AND t2.daily_date = '12-26-2022'
WHERE t2.id_number IS NULL