Get last previous and first next

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get last previous and first next

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-25 : 14:17:55

Hello all,
having this records:

EventID - DataRef

17 2012-06-20 10:00:00.000
21 2012-06-22 12:00:00.000
13 2012-06-10 10:00:00.000
15 2012-06-02 10:00:00.000
16 2012-06-26 10:00:00.000
25 2012-07-01 10:00:00.000
29 2012-06-30 15:00:00.000

and an EventID, for example 16, I have to write 2 queries that gives me the last previus EventID and the next following EventID, based them on DataRef.

For example, for the EventID=16, with DataRef=2012-06-26 10:00:00.000
the last previous is EventID=21, with DataRef=2012-06-22 12:00:00.000
while the next following is
EventID=29 with DataRef=2012-06-30 15:00:00.000

How can I write these 2 queries?

Thanks in advance.

Luigi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-25 : 14:27:44

[code]-- sample data only
DECLARE @t TABLE(EventID INT, DataRef SMALLDATETIME)
INSERT @t
SELECT 17, '2012-06-20 10:00' UNION ALL SELECT
21, '2012-06-22 12:00' UNION ALL SELECT
13, '2012-06-10 10:00' UNION ALL SELECT
15, '2012-06-02 10:00' UNION ALL SELECT
16, '2012-06-26 10:00' UNION ALL SELECT
25, '2012-07-01 10:00' UNION ALL SELECT
29, '2012-06-30 15:00'

-- actual query, substitute your table name for @t
;WITH CTE (EventID, DataRef, rownum)
AS (SELECT *, ROW_NUMBER() OVER (ORDER BY DataRef) rownum FROM @t)
SELECT Curr.EventID CurrentID, Curr.DataRef CurrentDataRef,
Prev.EventID PreviousID, Prev.DataRef PreviousDataRef,
Next.EventID NextID, Next.DataRef NextDataRef
FROM CTE Curr
LEFT JOIN CTE Prev ON Curr.rownum=Prev.rownum+1
LEFT JOIN CTE NEXT ON Curr.rownum=Next.rownum-1[/code]

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-25 : 14:41:31

Great, thank you very much Rob.

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-27 : 08:19:37

Just a little adding Rob.
How can I change your query if in this table I don't have in input only the date and not the EventID?

For example, in the case above, if I have the date '2012-06-10 10:00:00.000'
I must find:

Previous -> 15 2012-06-02 10:00:00.000
Next -> 17 2012-06-20 10:00:00.000

Luigi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-27 : 09:13:16

[code]DECLARE @Sample TABLE
(
EventID INT,
DataRef SMALLDATETIME
);

INSERT @Sample
(
EventID,
DataRef
)
VALUES (17, '20120620 10:00'),
(21, '20120622 12:00'),
(13, '20120610 10:00'),
(15, '20120602 10:00'),
(16, '20120626 10:00'),
(25, '20120701 10:00'),
(29, '20120630 15:00');

-- SwePeso
SELECT p.EventID AS PreviousEventID,
p.DataRef AS PreviousDataRef,
s.EventID AS CurrentEventID,
s.DataRef AS CurrentDataRef,
n.EventID AS NextEventID,
n.DataRef AS NextDataRef
FROM @Sample AS s
OUTER APPLY (
SELECT TOP(1) *
FROM @Sample AS p
WHERE p.DataRef < s.DataRef -- Can be EventID instead
ORDER BY p.DataRef DESC
) AS p
OUTER APPLY (
SELECT TOP(1) *
FROM @Sample AS n
WHERE n.DataRef > s.DataRef -- Can be EventID instead
ORDER BY n.DataRef
) AS n
ORDER BY s.DataRef[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-27 : 09:15:23

And for SQL Server 2012...

DECLARE	@Sample TABLE
	(
		EventID INT,
		DataRef SMALLDATETIME
	);

INSERT	@Sample
	(
		EventID,
		DataRef
	)
VALUES	(17, '20120620 10:00'),
	(21, '20120622 12:00'),
	(13, '20120610 10:00'),
	(15, '20120602 10:00'),
	(16, '20120626 10:00'),
	(25, '20120701 10:00'),
	(29, '20120630 15:00');

-- SwePeso
SELECT		LAG(EventID) OVER (ORDER BY DataRef) AS PreviousEventID,
		LAG(DataRef) OVER (ORDER BY DataRef) AS PreviousDataRef,
		EventID AS CurrentEventID,
		DataRef AS CurrentDataRef,
		LEAD(EventID) OVER (ORDER BY DataRef)  AS NextEventID,
		LEAD(DataRef) OVER (ORDER BY DataRef)  AS NextDataRef
FROM		@Sample
ORDER BY	DataRef

N 56°04'39.26"
E 12°55'05.63"

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-27 : 09:24:09

In the first case (SQL Server 2008) how can I use my datetime parameter?
In this way it returns me the entire table.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 10:38:12

just add a WHERE condition


SELECT		p.EventID AS PreviousEventID,
		p.DataRef AS PreviousDataRef,
		s.EventID AS CurrentEventID,
		s.DataRef AS CurrentDataRef,
		n.EventID AS NextEventID,
		n.DataRef AS NextDataRef
FROM		@Sample AS s
OUTER APPLY	(
			SELECT TOP(1)	*
			FROM		@Sample AS p
			WHERE		p.DataRef < s.DataRef -- Can be EventID instead
			ORDER BY	p.DataRef DESC
		) AS p
OUTER APPLY	(
			SELECT TOP(1)	*
			FROM		@Sample AS n
			WHERE		n.DataRef > s.DataRef -- Can be EventID instead
			ORDER BY	n.DataRef
		) AS n
WHERE s.DataRef >=@Dateparam
AND s.DataRef < @Dateparam+1
ORDER BY	s.DataRef

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-27 : 10:46:37

Exactly, thank you Visakh.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 11:09:22

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources