P粉2538003122023-09-01 09:56:28
Typically, to test this situation, you need to see if the date you are interested in is greater than the check-in date and less than the check-out date. For example
WHERE checkin <= '2022-12-14' and checkout >= '2022-12-14'
To solve the situation where the check-out date can be empty but still "OK", we just replace the empty value with a value that meets the condition.
SELECT * FROM my_history WHERE checkin <= '2022-12-14' and COALESCE(checkout,'2022-12-14') >= '2022-12-14'
P粉3176793422023-09-01 09:17:04
Old answers have been deleted due to changes in requirements. In the rush, there was no time to explain.
SELECT param.check_date, COUNT(DISTINCT p.id) FROM ( SELECT timestamp '2022-12-15 00:00' AS check_date UNION ALL SELECT timestamp '2022-12-17 00:00' AS check_date UNION ALL SELECT timestamp '2022-12-18 00:00' AS check_date ) AS param CROSS JOIN person AS p INNER JOIN my_history AS h ON h.person_id = p.id AND h.when_created >= COALESCE( ( SELECT when_created FROM my_history WHERE person_id = p.id AND when_created <= check_date ORDER BY when_created DESC LIMIT 1 ), check_date ) AND h.when_created < check_date + INTERVAL 1 DAY AND h.action = 'checked_in' GROUP BY param.check_date