Search for closes index on SQL table-CodePudding

I have a hypothetical SQL table "EVENTS", with two columns, a UUID index column, and a DateTime column, The table is populated with values ranging from 1900-01-01 to today, it is not ordered, there are numerous dates missing.

The query that I have to run is basically 'retrieve all events that happened at the requested date (start to the end of the day) or the closest previous date'

If I were looking for all events in a day that I know that exists in the database it would be something as simple as:

SELECT * FROM Events e
WHERE
e.date BETWEEN $START_OF_DAY AND $END_OF_DAY;

But if that date doesn't exist I must retrieve the latest date up to the requested date.

CodePudding user response：

Grab current day, but if no records found, will return all records from the nearest previous day with records. So in my sample data, Jan 2 returns 3 events dated Jan 1

SQL Server Solution

DECLARE @Input DATE = '2022-01-02' /*Try Jan 1,2,3, or 4*/
DROP TABLE IF EXISTS #Event

CREATE TABLE #Event (ID INT IDENTITY(1,1),EventDateTime DATETIME)
INSERT INTO #Event
VALUES 
('2022-01-01 08:00')
,('2022-01-01 09:00')
,('2022-01-01 10:00')
,('2022-01-03 12:00')

SELECT TOP (1) WITH TIES *
FROM #Event AS A
CROSS APPLY (SELECT EventDate = CAST(EventDateTime AS DATE)) AS B
WHERE B.EventDate <= @Input
ORDER BY B.EventDate DESC

CodePudding user response：

Calculate the most recent date, and do a self join. Although I'm using MYSQL, I believe this is the most generic workaround

CREATE TABLE d0207Event (ID INT ,EventDateTime DATETIME)

INSERT INTO d0207Event
VALUES 
(1,'2022-01-01 08:00')
,(2,'2022-01-01 09:00')
,(3,'2022-01-01 10:00')
,(4,'2022-01-03 12:00')

INSERT INTO d0207Event
VALUES 
(5, '2021-12-12 08:00');

select t1.*
  from d0207Event t1,
  (
    select min(t1.dat) mindat
      from (
        select t1.*,
               DATEDIFF('2022-01-02', cast(t1.EventDateTime as date)) dat
          from d0207Event t1
    ) t1
     where t1.dat >= 0
 ) t2
 where DATEDIFF('2022-01-02', cast(t1.EventDateTime as date)) = t2.mindat
;

There are also many advanced syntaxes that can solve this problem better, depending on which DB you use and your specific application scenario