Problem with a min/max date query
-
Hi, i'm having problems with writing a pretty basic query:
I have a table called #mydates, i want to select following:
IF MAX(mydate)<GETDATE() THEN MAX(mydate) ELSE ( MIN(mydate) WHERE mydate>=GETDATE ) END IF
To put it in words, if greatest mydate is less then current date, select most recent mydate. Otherwise if mydate is greater or equal to the current date, select the most recent mydate that is closest to the current date.
Basic table structure:
CREATE TABLE [#mydates](
[id] [int] NOT NULL,
[mydate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [#mydates] (id, [mydate])
SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2012-12-05' UNION ALL
SELECT 3,'2013-02-15'
SELECT * FROM #mydates
Thanks for your time
-
select
case when MAX(mydate) < GETDATE() THEN MAX(mydate) ELSE MIN(Mydate) end as mydate
from #mydates where mydate >= GETDATE()
-
Yes, but you've missed that IF MAX(mydate)<GETDATE() THEN MAX(mydate)
Your WHERE clause truncates all dates that are less then current date "where mydate >= GETDATE()"
This can be seen with this values
INSERT INTO [#mydates] (id, [mydate])SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2012-12-05' UNION ALL
SELECT 3,'2012-02-15'
The desired result would be
'2012-12-05'
Btw, thanks for a fast reply.
-
That's not your original data set, the last date was 2013-02-15.
CREATE TABLE [#mydates](
[id] [int] NOT NULL,
[mydate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [#mydates] (id, [mydate])
SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2012-12-05' UNION ALL
SELECT 3,'2013-02-15'
SELECT * FROM #mydates
-
That is true, i had to change the data to demonstrate the original IF specifications that i specified above.
Hmm, to clarify it lets look at two sets:
With the table below:
INSERT INTO [#mydates] (id, [mydate])SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2012-12-05' UNION ALL
SELECT 3,'2013-02-15' UNION ALL
SELECT 4,'2013-03-15'
Desired result would be '2013-02-15'
With the table below:
INSERT INTO [#mydates] (id, [mydate])SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2012-12-05' UNION ALL
SELECT 3,'2012-02-15'
Desired result would be '2012-12-05'
I think my IF specs are correct...
-
Does it make any sense at all?
-
yes it makes sense, I am busy with work now, when I get a spare moment I will re-look at this unless someone else beats me to it
-
naturally, thanks 🙂
-
Just to add, I simplified your requirements.
To put it in words, if greatest mydate is less then current date, select most recent mydate. Otherwise if mydate is greater or equal to the current date, select the most recent mydate that is closest to the current date.
1. If greatest mydate is less then current date then the most recent mydate will be the closest in time to current date.
2. If mydate is greater or equal to the current date then return the mydate closest in time to current date.
Therefore: Return the mydate closest in time to current date.
Did I understand the requirements correctly?
Edit to add:
What is your desired output from this data set?
INSERT INTO [#mydates] (id, [mydate])SELECT 1,'2012-01-03' UNION ALL
SELECT 2,'2013-01-24' UNION ALL
SELECT 3,'2013-02-15'
-
If your desired output above is 2013-01-24 then my first solution is correct.
If your desired output above is 2013-02-15 then the following will work.
SELECTTOP 1 *
FROM
#mydates
ORDER BY
CASE
WHEN mydate > GETDATE() THEN 0
ELSE 1
END,
ABS(DATEDIFF(SECOND, GETDATE(), mydate));
-
@sean Pearce - Sorry was away at lunch, the desired result would be '2013-02-15'
Yeah! The last query you've posted worked really well! Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply