Query for getting countable matching entries between dates and join them to read states of user

user_19510857 0
Viewed 45

i would like to count the entries of my record table, filtered by a given period by valid_from and valid_till or if valid_till is null

Records

id name valid_from valid_till
1 A 2022-07-01 2022-07-05
2 B 2022-07-02 2022-07-05
3 C 2022-07-02 2022-07-04
4 D 2022-07-03 null
5 E 2022-07-03 2022-07-03

Now let's assume that I get the following period (2022-06-30 to 2022-07-07) from the client. Now I want to find out all records that match this time period and the number of hits for each date in this period, not only for valid_from and valid_till.

I expect the following results:

date matches
2022-06-30 0
2022-07-01 1
2022-07-02 3
2022-07-03 5
2022-07-04 4
2022-07-05 3
2022-07-06 0
2022-07-07 0

Furthermore, I would like to extend the whole scenario by knowing how many matches have already been read by the current user. For this, there is an additional table that holds the information per user and per record.

Viewstates

id record user
1 1 X
2 1 Y
3 2 X
4 3 Y
5 4 X
6 4 Y
7 4 Z
8 5 X
9 5 Y
10 5 Z

Assuming I am now user X then I would expect the following result:

date matches already_read_by_me
2022-06-30 0 0
2022-07-01 1 1
2022-07-02 3 2
2022-07-03 5 4
2022-07-04 4 3
2022-07-05 3 3
2022-07-06 0 0
2022-07-07 0 0

Unfortunately, I currently really have no idea how to implement and solve this.

My actually coding attempt looks like that

SELECT
    COUNT(r.id) as matches,
    COUNT(vs.record_id) as already_ready_by_me
FROM
    records r
LEFT JOIN
    (
        SELECT id AS viewstate, record_id FROM viewstates WHERE user = 'xxx'
    ) vs ON r.id = vs.record_id
WHERE
    r.valid_from >= '2022-07-01'
    AND (r.valid_till <= '2022-07-10' OR r.valid_till IS NULL)
ORDER BY
    valid_from
DESC;

Hope here is someone who can help me.

Thanks a lot.

BR, Sven

mysql sql
1 Answers

The first thing you will need to do is to concatenate the input dates as in the selected_range subquery.

In my understanding the following query is the correct one, because if a record has valid_till = null it means that it is still valid until NOW right? So in your example, 2022-07-06 and 2022-07-07 would be a match, because record D would be valid from 2022-07-03 to NOW

############## ONLY MATCHES
SELECT selected_range.date,
       COUNT(records.id) matches
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, CAST(NOW() AS DATE))
GROUP BY selected_range.date        

############# MATCHES READ BY USER
SELECT selected_range.date,
       COUNT(records.id) matches,
       COUNT(viewstates.USER) already_read_by_me
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, CAST(NOW() AS DATE))
LEFT JOIN viewstates ON viewstates.record = records.id 
                    AND viewstates.USER = 'X' 
GROUP BY selected_range.date

But your expected result is possible, however the logic that i was able to notice was that for the record with valid_till=null be a match, there must be other record that has valid_till NOT null. For example, for the 2022-07-05, the D record matched, because the records A and B also matched.

But for the 2022-07-06 and 2022-07-07 the record D was not considered, i understood that it was because there are not other records that matched.

So I implemented this logic:

######## ONLY MATCHES
SELECT selected_range.date,
       COUNT(records.id) matches
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON (selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, records.valid_from))
                 OR (EXISTS (SELECT 1 
                             FROM records records_inner
                             WHERE (selected_range.date BETWEEN records_inner.valid_from 
                                                        AND records_inner.valid_till)
                      AND selected_range.date >= records.valid_from
                      AND records.valid_till IS NULL
                 ))
GROUP BY selected_range.date 
                

######## MATCHES READ BY THE USER
SELECT selected_range.date,
       COUNT(records.id) matches,
       COUNT(viewstates.user) already_read_by_me
FROM (        SELECT '2022-06-30' date
    UNION ALL SELECT '2022-07-01'
    UNION ALL SELECT '2022-07-02'
    UNION ALL SELECT '2022-07-03'
    UNION ALL SELECT '2022-07-04'
    UNION ALL SELECT '2022-07-05'
    UNION ALL SELECT '2022-07-06'
    UNION ALL SELECT '2022-07-07') selected_range
LEFT JOIN records ON (selected_range.date BETWEEN records.valid_from 
                 AND COALESCE (records.valid_till, records.valid_from))
                 OR (EXISTS (SELECT 1 
                             FROM records records_inner
                             WHERE (selected_range.date BETWEEN records_inner.valid_from 
                                                        AND records_inner.valid_till)
                      AND selected_range.date >= records.valid_from
                      AND records.valid_till IS NULL
                 ))
LEFT JOIN viewstates ON viewstates.record = records.id 
                    AND viewstates.USER = 'X'
GROUP BY selected_range.date
user_192235860
Related