0

So I have 2 tables called listings and logs with the following headers:

Listings table:('D'=>'Draft','N'=>'Action','Y'=>'Publish')

enter image description here

Logs Table:

enter image description here

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.

4
  • "This does not return the correct output" Could you should what the correct output should be?
    – bbaird
    Oct 13 at 13:22
  • @bbaird so suppose on October 1 there were 10 listings under draft, October 2 there are 4. So here 6 of them I changed to some other status. Now if I want to see what was the total on October 1st(SELECT SUM(status = 'D') AS draft, SUM(status = 'N') AS action, SUM(status = 'Y') AS publish FROM crm_listings where updated_date between '2021-10-01' and '2021-10-01'), it will return 6 under the changed headings and not 10 under draft.
    – Jay Modi
    Oct 13 at 18:17
  • Please update your answer to include sample data & expected output - descriptions are of limited value if people are trying to troubleshoot.
    – bbaird
    Oct 13 at 18:38
  • Please consider adding a minimal reproducible example to your question via the edit link. Be sure to include the table DDL, some sample data, and expected output.
    – Hannah Vernon
    Oct 13 at 21:09
0
between '2021-05-29' and '2021-05-29'

That's "between midnight and the same midnight", for a total of 1 second. Change to

updated_date >= '2021-05-29' meaning "since midnight of the morning of that date

or

    updated_date >= '2021-05-29'
AND updated_date  < '2021-05-29' + INTERVAL 1 DAY

to mean exactly that one day.

CURDATE() can be used for midnight of today; CURDATE() - INTERVAL 1 DAY is the beginning of yesterday. So just "yesterday" is best phrased:

    updated_date >= CURDATE() - INTERVAL 1 DAY
AND updated_date  < CURDATE()

The following will speed up your query:

INDEX(update_date)
5
  • No that date is just an example. So I want to check for the matching logtime and update_time, and subtract the status_to in that particular timeframe so 15(todays total that I got from listings table)-5(changed to action between today and yesterday whcih is recorded in my status_to in logs table) 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 is recorded in my status_from)
    – Jay Modi
    Oct 13 at 17:31
  • Or maybe an even simpler clarification would be suppose on 1st October my total records for action is 10 which I'm getting via SELECT SUM(status = 'D') AS draft now on 2nd October it changed to 15. Now I want to retrieve the total that was recorded on the 1st of October, so I want to show 10 as output for when the condition is to show total listings of action on 1st October
    – Jay Modi
    Oct 13 at 17:37
  • So, if you had a summary table that held 10 for Oct 1 and 5 for Oct 2, you could get the total for any range of days? See mysql.rjweb.org/doc.php/summarytables
    – Rick James
    Oct 13 at 17:43
  • Yes so if you see my question you can see I have a table like that that is recording all the changes made and when they were made. Now I need to somehow make it so that when user suppose selcts October 1, the listing table has to check with log table for all changes made on that particular date and accordingly subtract or add to the current total which its showing, which in my case is today's total
    – Jay Modi
    Oct 13 at 18:11
  • @JayModi - Either compute the daily totals at the end of the day. Or Update a summary table with IODKU.
    – Rick James
    Oct 13 at 19:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.