Getting the total sum of data in a particular timeframe-CodePudding

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

Logs Table:

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` where updated_date between '2021-05-29' and '2021-05-29'

Basically I want to return all the records that happened between a particular timeframe.

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.

CodePudding user response：

I suppose all listing entries appear in the log_table, even if it was created but no status change since then.

So it needs to lookup the log table only for the record with latest logtime up to the query date for each refno, the status_to for the selected record is the standing status for the date.

To archive this, sort the log_table by refno and logtime in descending order and find the first record for each refno.

SELECT SUM(status_to = 'Action'), SUM(status_to = 'Publish') FROM (
    SELECT refno, status_to, ROW_NUMBER() OVER(PARTITION BY refno ORDER BY logtime DESC) AS RN
    FROM log_table 
    WHERE logtime <= '2021-05-29 23:59:59'
) r
WHERE r.RN = 1

or this for older MySQL servers

SELECT SUM(status_to = 'Action'), SUM(status_to = 'Publish') FROM (
    SELECT refno, status_to, CASE WHEN @RF != refno THEN @RN := 1 ELSE @RN := @RN   1 END AS RN, @RF := refno
    FROM log_table 
    JOIN ( SELECT @RN := 0 , @RF := '' ) f
    WHERE logtime <= '2021-05-29 23:59:59'
    ORDER BY refno ASC, logtime DESC
) r
WHERE r.RN = 1

If log_table is huge and this query is important, do consider to create another table to store such daily statistics.