CODEWITHSUNDEEP
sqlsql-server
Jonas Olesen
Jonas Olesen

Reputation: 568

SQL Get closest date, in both directions

I'm trying to figure out an efficient way of querying a table, i need to output the row closest to a given date, in both directions mind you.

I have a solution for the closest day in the future

SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE EstimatedArrivalDate < @date
   AND Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY EstimatedArrivalDate DESC

This gets the closest future date, but i need to check for past dates also.

Upvotes: 3

Views: 277

Answers (5)

danish
danish

Reputation: 5610

You can make use of Datediff and Abs in combination.

select abs(DATEDIFF(second,<dateColumn>,<dateParameter>)) result,<otherColumns> 
from <tableName> order by  result

The first record will be the closest one.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If you want this to be efficient, you will need the right indexes. If so, I think a more complex query might be in order:

SELECT TOP 1 id
FROM ((SELECT TOP 1 id, EstimatedArrivalDate
       FROM [dbo].[Shipments] 
       WHERE EstimatedArrivalDate < @date AND
             Complete = 0 
             RegNoTrailer = @regNo
       ORDER BY EstimatedArrivalDate DESC
      ) UNION ALL
      (SELECT TOP 1 id, EstimatedArrivalDate
       FROM [dbo].[Shipments] 
       WHERE EstimatedArrivalDate < @date AND
             Complete = 0 
             RegNoTruck = @regNo 
       ORDER BY EstimatedArrivalDate DESC
      ) UNION ALL
      (SELECT TOP 1 id, EstimatedArrivalDate
       FROM [dbo].[Shipments] 
       WHERE EstimatedArrivalDate > @date AND
             Complete = 0 
             RegNoTrailer = @regNo
       ORDER BY EstimatedArrivalDate ASC
      ) UNION ALL
      (SELECT TOP 1 id, EstimatedArrivalDate
       FROM [dbo].[Shipments] 
       WHERE EstimatedArrivalDate > @date AND
             Complete = 0 
             RegNoTruck = @regNo 
       ORDER BY EstimatedArrivalDate ASC
      )
     )
ORDER BY ABS(DATEDIFF(second, EstimatedArrivalDate, @Date));

For this query, you want two indexes: Shipments(RegNoTruck, Complete, EstimatedArrivalDate) and Shipments(RegNoTrailer, Complete, EstimatedArrivalDate).

By splitting the OR conditions into two separate subqueries, each subquery can use a different index.

Upvotes: 2

mihi
mihi

Reputation: 11

Use DATEDIFF() and ABS() to get the closest date difference

SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY ABS(DATEDIFF(SECOND, @date, EstimatedArrivalDate))

Upvotes: 0

Raphael Murimigwa
Raphael Murimigwa

Reputation: 66

Try using DATEDIFF function to find difference between 2 dates. You would also need to add the ABS() function to cater for both past and future dates

SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY ABS(DATEDIFF(D,EstimatedArrivalDate,@date)) ASC

Upvotes: 1

Mazhar
Mazhar

Reputation: 3837

Will this work?

   SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE EstimatedArrivalDate > @date --change to >
   AND Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY EstimatedArrivalDate ASC --change to ASC

Or both together using UNION

SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE EstimatedArrivalDate < @date
   AND Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY EstimatedArrivalDate DESC
UNION
   SELECT  top 1 id
   FROM [dbo].[Shipments] 
   WHERE EstimatedArrivalDate > @date --change to >
   AND Complete = 0 
   AND (RegNoTrailer = @regNo OR RegNoTruck = @regNo) 
   ORDER BY EstimatedArrivalDate ASC --change to ASC

Upvotes: 0

Related Questions