Date closest to current date in SQL (prior dates or future dates)

Question

I am running into a bit of an issue with finding out how to look for dates closest to the current date. I looked at this among others: Get closest date to current date sql

I also looked here: https://bytes.com/topic/sql-server/answers/79502-get-closest-date

I am using MS SQL 2012 and there have been many questions asked about this, so I apologize for bringing it back up. I can't seem to get my query to work.

Basically I have a table as follows:

ITEMNMBR | MINDATE    | MAXDATE    | CLOSESTDATE
------------------------------------------------
123456   | 2017-10-15 | 2017-11-04 | NULL
654321   | 2017-09-29 | 2017-12-08 | NULL

The current date would be today, '2017-10-03'. We would find for item number '123456' the closest date of purchase is 2017-10-15. For item number '654321' the closest date of purchase is 2017-09-29 as that happened much more recently than how long it will take for the next purchase to take effect (hence I am looking at an absolute value of the difference between the dates). You'll have to forgive me, but the query I am including doesn't include the "CLOSESTDATE" column. I've included it there to let you know that any of my calculations have rendered that column NULL. So here's what I have:

--Lines commented below are not used in the current iteration of the query
--DECLARE @dt DATETIME
--SET @dt = GETDATE()

SELECT
    I.ITEMNMBR,
    MIN(PDATE1) AS MINDATE,
    MAX(PDATE1) AS MAXDATE
FROM dbo.IV00101 I 
LEFT OUTER JOIN
    (SELECT P.[Item Number], 
    P.[Req Date] AS PDATE1 
    FROM dbo.Purchases P
    WHERE ((P.[Document Status] = 'Open') AND 
    (P.[POStat] <> 'Closed') AND 
    (P.[POStat] <> 'Received')) AND P.[Req Date] >= DATEADD(d, -15, 
    DATEDIFF(d, 0, GETDATE()))
    ) AS P ON P.[Item Number]= I.ITEMNMBR
WHERE P.[Item Number] = '123456'
GROUP BY 
    I.ITEMNMBR
ORDER BY MINDATE DESC

When I run this query, I get the table I outlined previously, minus the "CLOSESTDATE" column. The "CLOSESTDATE" column is what I want to use to display what date of purchase is closest to TODAY. Basically, if a date of purchase happened three days ago and the next date of purchase is a month out, then I want to show the date of purchase from three days ago. Also, the query can be written without using a subquery, but I was using other calculations within the subquery prior to reverting back to the rather simplistic original query. Thus, the query can be written like this:

SELECT
    I.ITEMNMBR,
    MIN(P.[Req Date]) AS MINDATE,
    MAX(P.[Req Date]) AS MAXDATE
FROM dbo.IV00101 I 
LEFT OUTER JOIN
    Purchases P ON P.[Item Number] = I.ITEMNMBR
WHERE P.[Item Number] = '123456' ((P.[Document Status] = 'Open') AND 
    (P.[POStat] <> 'Closed') AND 
    (P.[POStat] <> 'Received')) AND P.[Req Date] >= DATEADD(d, -15, 
    DATEDIFF(d, 0, GETDATE()))
GROUP BY 
    I.ITEMNMBR
ORDER BY MINDATE DESC

Lastly, as you can see, I have a date constraint for the past 15 days so that anything older than that won't show up at all.

Many thanks in advance!

Your query is way more complicated than your sample data -- making it hard to figure out the question that you are really asking. You might start by providing accurate sample data, representative of the situations that might arise. — Gordon Linoff
Do you just need to know this across the two dates in each row? — iamdave
I am confused by your complicated query by what seems to be a relative simple question. Are you just trying to find whether the MINDATE or MAXDATE is closest to today given a ITEMNMBR? And if the closest day is greater than 15, then NULL them? Or is the 15 days something you're just telling us that is part of your original query? — Simon

iamdave iamdave · Accepted Answer · 2017-10-03T15:38:37

I have taken your data and schema and made an approximation of what I believe your underlying data actually looks like. From that, your problem is actually very simple:

declare @t table(ItemNumber int, ReqDate datetime, DocumentStatus nvarchar(100), POStat nvarchar(100))
insert into @t values
 (123456,'2017-10-15','Open','Not Closed')
,(123456,'2017-11-04','Open','Not Closed')
,(654321,'2017-09-29','Open','Not Closed')
,(654321,'2017-12-08','Open','Not Closed')

,(123456,'2017-10-11','Open','Closed')
,(123456,'2017-11-01','Closed','Not Closed')
,(654321,'2017-09-21','Closed','Not Closed')
,(654321,'2017-12-01','Open','Received');

select t.ItemNumber
      ,min(t.ReqDate) as MinDate
      ,max(t.ReqDate) as MaxDate

            -- Find the difference in days for both Min and Max dates, converting to positive numbers where negative,
      ,case when abs(datediff(d,min(t.ReqDate),getdate())) < abs(datediff(d,max(t.ReqDate),getdate()))
            then min(t.ReqDate)         -- And then return the appropriate one.
            else max(t.ReqDate)
            end as ClosestDate
from @t t
where t.DocumentStatus = 'Open'
    and t.POStat not in('Closed','Received')
    and t.ReqDate >= dateadd(d,-15,cast(getdate() as date))
group by t.ItemNumber
order by MinDate desc;

Output:

+------------+-------------------------+-------------------------+-------------------------+
| ItemNumber |         MinDate         |         MaxDate         |       ClosestDate       |
+------------+-------------------------+-------------------------+-------------------------+
|     123456 | 2017-10-15 00:00:00.000 | 2017-11-04 00:00:00.000 | 2017-10-15 00:00:00.000 |
|     654321 | 2017-09-29 00:00:00.000 | 2017-12-08 00:00:00.000 | 2017-09-29 00:00:00.000 |
+------------+-------------------------+-------------------------+-------------------------+

Date closest to current date in SQL (prior dates or future dates)

3 Answers