July 27, 2012 at 6:47 am
Hello all,
having a table like this one:
ID - DateRef - GroupID
17 2012-06-20 10:00:00.000 463
21 2012-06-22 12:00:00.000 463
13 2012-06-10 10:00:00.000 463
15 2012-06-02 10:00:00.000 463
15 2012-06-08 10:00:00.000 463
16 2012-06-26 10:00:00.000 463
25 2012-07-01 10:00:00.000 463
29 2012-06-30 15:00:00.000 463
and a variable with this value '2012-06-08 15:00:00.000'
I should get the first next record, and the last previous.
For this example, I should get:
15 2012-06-08 10:00:00.000 463
13 2012-06-10 10:00:00.000 463
Anyone has idea how to write a query in this way?
Thanks in advance.
Luigi
July 27, 2012 at 7:17 am
Here's one way!
-- set up our test table and variable
create table test1 (id int, dateref datetime, groupid int)
insert into test1 (id, dateref, groupid) values (17, '2012-06-20 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (21, '2012-06-22 12:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (13, '2012-06-10 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (15, '2012-06-02 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (15, '2012-06-08 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (16, '2012-06-26 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (25, '2012-07-01 10:00:00.000', 463)
insert into test1 (id, dateref, groupid) values (29, '2012-06-30 15:00:00.000', 463)
declare @testvar datetime
set @testvar = '2012-06-08 15:00:00.000'
-- select the closest previous and next
select top 1 id, dateref, groupid from test1 where dateref < @testvar order by dateref desc
select top 1 id, dateref, groupid from test1 where dateref > @testvar order by dateref
-- if you want the case where you want the record that lands on the exact time, just
-- use '=' as well
-- select the closest previous and next
select top 1 id, dateref, groupid from test1 where dateref <= @testvar order by dateref desc
select top 1 id, dateref, groupid from test1 where dateref >= @testvar order by dateref
-- if you want the results into one resultset, I would use either 'union' or 'union all'.
select id, dateref, groupid from
(
select top 1 id, dateref, groupid from test1 where dateref <= @testvar order by dateref desc
) prev
union all
select id, dateref, groupid from
(
select top 1 id, dateref, groupid from test1 where dateref >= @testvar order by dateref
) next
July 27, 2012 at 7:21 am
Here's a couple of ways:
DROP TABLE #Sample
CREATE TABLE #Sample (ID int, DateRef datetime, GroupID int)
INSERT INTO #Sample (ID, DateRef, GroupID)
SELECT 17, '2012-06-20 10:00:00.000', 463 UNION ALL
SELECT 21, '2012-06-22 12:00:00.000', 463 UNION ALL
SELECT 13, '2012-06-10 10:00:00.000', 463 UNION ALL
SELECT 15, '2012-06-02 10:00:00.000', 463 UNION ALL
--SELECT 15, '2012-06-08 10:00:00.000', 463 UNION ALL -- duplicate ID
SELECT 16, '2012-06-26 10:00:00.000', 463 UNION ALL
SELECT 25, '2012-07-01 10:00:00.000', 463 UNION ALL
SELECT 29, '2012-06-30 15:00:00.000', 463
SELECT s.*
FROM #Sample s
CROSS APPLY (SELECT Dateref = CAST('2012-06-08 15:00:00.000' AS DATETIME)) p
CROSS APPLY (SELECT TOP 1 ID FROM #Sample l WHERE l.Dateref < p.Dateref ORDER BY l.Dateref DESC) l
CROSS APPLY (SELECT TOP 1 ID FROM #Sample n WHERE n.Dateref > p.Dateref ORDER BY n.Dateref ASC) n
WHERE s.ID IN (l.ID, n.ID)
ORDER BY s.Dateref
SELECT *
FROM (
SELECT TOP 1 *
FROM #Sample
WHERE Dateref < CAST('2012-06-08 15:00:00.000' AS DATETIME)
ORDER BY Dateref DESC
) a
UNION ALL
SELECT *
FROM (
SELECT TOP 1 *
FROM #Sample
WHERE Dateref > CAST('2012-06-08 15:00:00.000' AS DATETIME)
ORDER BY Dateref ASC
) b
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 7:28 am
In the case of one result set:
select id, dateref, groupid from
(
select top 1 id, dateref, groupid from test1 where dateref <= @testvar order by dateref desc
) prev
union all
select id, dateref, groupid from
(
select top 1 id, dateref, groupid from test1 where dateref >= @testvar order by dateref
) next
I need to obtain values also for the next record in the case it does not exist (for example if i pass a datetime more recent than every record in the table, where I get only the previous one).
How can I change this?
Luigi
July 27, 2012 at 7:32 am
GigiMi (7/27/2012)
...I need to obtain values also for the next record in the case it does not exist (for example if i pass a datetime more recent than every record in the table, where I get only the previous one).How can I change this?
Luigi
What do you want the row to contain?
The simplest way to do this would be to check if only one row is returned, and if so, duplicate it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 7:40 am
For example, if I pass the date '2012-07-02 10:00:00'
(more recent than any other record) I'd like to obtain something like this:
PreviousID - PreviousDateRef - NetxID - NextDateRef
25 2012-07-01 10:00:00.000 NULL NULL
Luigi
July 27, 2012 at 7:48 am
GigiMi (7/27/2012)
For example, if I pass the date '2012-07-02 10:00:00'(more recent than any other record) I'd like to obtain something like this:
PreviousID - PreviousDateRef - NetxID - NextDateRef
25 2012-07-01 10:00:00.000 NULL NULL
Luigi
That's only one row. What would the other row be?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 7:49 am
I need to put everything (values for next and previous) in only one row, if possible.
Luigi
July 27, 2012 at 8:04 am
GigiMi (7/27/2012)
I need to put everything (values for next and previous) in only one row, if possible.Luigi
Ah I see, sorry my mistake. Try this:
SELECT l.ID, l.Dateref, n.ID, n.Dateref
FROM (SELECT Dateref = CAST('2013-06-08 15:00:00.000' AS DATETIME)) p
OUTER APPLY (SELECT TOP 1 * FROM #Sample l WHERE l.Dateref < p.Dateref ORDER BY l.Dateref DESC) l
OUTER APPLY (SELECT TOP 1 * FROM #Sample n WHERE n.Dateref > p.Dateref ORDER BY n.Dateref ASC) n
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 8:39 am
Perfect, thank you very much Chris.
Luigi
July 27, 2012 at 8:43 am
You're welcome, thanks for the generous feedback 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply