So I have 2 tables called listings and logs with the following headers:
The logs table makes a new record every status change made and passes the current timestamp as the logtime, and the listings table changes/updates the status and updates its update_date. Now to get the total listings as of today I'm using the following statement:
SELECT SUM(status = 'D') AS draft, SUM(status = 'N') AS action, SUM(status = 'Y') AS publish FROM `crm_listings`
And this returns all the count data for status as of the current day.
Now this is where it gets confusing for me. So suppose today the count under action is 10 and tomorrow it'll be 15, and I want to retrieve the total that was present yesterday(10), I use the following statement:
SELECT SUM(status = 'D') AS draft, SUM(status = 'N') AS action, SUM(status = 'Y') AS publish FROM `crm_listings` where updated_date between '2021-05-29' and '2021-05-29'
This does not return the correct output. So basically from what I understand here is that I will have to check for the matching logtime and update_time, and subtract the status_to in that particular timeframe so 15(todays total)-5(changed to action between today and yesterday whcih will be recorded in my status_to) and add status_from if yesterday it was 15 while today it is 10 so 10(todays total)+5(changed to some other status between today and yesterday whcih will be recorded in my status_from)?
Note: Refno isn't unique in my logs table since a product with the same refno can be marked as publish 1 day and unpublish another, but it is unique in my listings table.