May 2, 2012 at 11:00 am
I'm trying to figure out how to write a MS SQL query that will return the closest 2 events (the previous and next one) in terms of date.
This is my table:
CREATE TABLE mytable
(
event int,
event_date date
)
insert mytable values (1,'2012-04-01')
insert mytable values (1,'2012-05-02')
insert mytable values (1,'2012-06-05')
insert mytable values (2,'2012-04-03')
insert mytable values (2,'2012-05-01')
insert mytable values (2,'2012-06-04')
So if the input is 2012-04-10 the result should be
event prev date next date
1 2012-04-012012-05-02
2 2012-04-032012-05-01
For input 2012-05-06 it should return
1 2012-05-022012-06-05
22012-05-012012-06-04
May 2, 2012 at 11:54 am
Something like this?
IF OBJECT_ID(N'tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable ;
CREATE TABLE #mytable
(event INT,
event_date DATE)
INSERT #mytable
VALUES (1, '2012-04-01') ,
(1, '2012-05-02') ,
(1, '2012-06-05') ,
(2, '2012-04-03') ,
(2, '2012-05-01') ,
(2, '2012-06-04')
DECLARE @InputDate DATE = '2012-04-10' ;
;
WITH Before
AS (SELECT TOP 2
*,
ROW_NUMBER() OVER (ORDER BY event_date DESC) AS R
FROM #mytable
WHERE event_date < @InputDate
ORDER BY event_date DESC),
[After]
AS (SELECT TOP 2
*,
ROW_NUMBER() OVER (ORDER BY event_date) AS R
FROM #mytable
WHERE event_date > @InputDate
ORDER BY event_date)
SELECT *
FROM Before
INNER JOIN [After]
ON Before.R = After.R
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 12:10 pm
One another way:
declare @inputdt date = '2012-04-10' ;
; with Edge1 as
(
select t.event ,Frst = MAX (t.event_date)
from mytable t
where t.event_date < @inputdt
group by t.event
)
,Edge2 as
(
select t.event ,Nxt = Min (t.event_date)
from mytable t
where t.event_date > @inputdt
group by t.event
)
select *
from Edge1 t1
full outer join Edge2 t2
on t1.event = t2.event
May 2, 2012 at 12:20 pm
Thank you very much! ColdCoffee introduced a more general approach that thoroughly solves my problem!
May 2, 2012 at 2:45 pm
Even more compact
select t.event , Frst = MAX (t.event_date) , min(CrsApp.mn)
from mytable t
cross apply (
select min(inr.event_date)
from mytable inr
where inr.event = t.event
and inr.event_date > @inputdt
) CrsApp (mn)
where t.event_date < @inputdt
group by t.event
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy