Getting dated records either side of a record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Getting dated records either side of a record?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-06-25 : 04:05:18

This one has been driving me nuts! I have a series of dated items. They are displayed to the user as a timeline, so if they select an item with 1st January 1980, I need to get the previous N records and following N records.

Because I'm stuck with msSql 2000 I can't use Top @N so need to find another way. I thought I'd cracked it yesterday by getting the ids of the records, repulling them and them ordering by the date. Unfortunately because it's a subquery the ids aren't the same, so the query runs out of the sub slect and pulls the right ids, but doesn't work in a subselect as the order by seems to be ignored.

Any found a way to get N records before and after a single record? Either by helping me with my sql below or any other technique

Here's my sql so far


DECLARE @UniqueId int
SET @UniqueId = 11

DECLARE @NoOff tinyint
SET @NoOff = 10/2 -- divide by 2 so we get half before and half after the current

SET ROWCOUNT @NoOff --Use rowcount so we only get the nearest records

DECLARE @ItemDate DateTime
SET @ItemDate = (SELECT [Date] FROM Memory WHERE UniqueId = @UniqueId)

--We need to be able to order the before records so we need to get
--the ids into a temporary table and then order those
DECLARE @Ids TABLE(UniqueId int)

INSERT INTO @Ids(UniqueId)
	SELECT UniqueId FROM Memory 
	    WHERE UniqueId <> @UniqueId 
	        AND DATEDiff(d,@ItemDate, [Date]) <= 0 
	    ORDER BY DATEDiff(d,@ItemDate, [Date]) DESC

SELECT * FROM @Ids

--this one pulls the correct records but in reverse order, hence trying to get the ids and re-ordering them
SELECT * FROM Memory 
	    WHERE UniqueId <> @UniqueId 
	        AND DATEDiff(d,@ItemDate, [Date]) <= 0 
	    ORDER BY DATEDiff(d,@ItemDate, [Date]) DESC

--Now we have the correct ids, re-order by date
SELECT DISTINCT UniqueId, Name, [Date], ThumbPath 
	FROM Memory 
	WHERE UniqueId in(SELECT * FROM @Ids) order by [date] 
--get requested record and those closest after
SELECT DISTINCT UniqueId, Name, [Date], ThumbPath 
    FROM Memory 
    WHERE 
        (UniqueId = @UniqueId OR UniqueId not in(SELECT * FROM @Ids))  
        AND DATEDiff(d,@ItemDate, [Date]) >= 0 
    ORDER BY [date] ASC

And the results, the first result set is the same query as the second result set(which is the correct results) but it pulls different ids!



(5 row(s) affected)
UniqueId
-----------
6
5
2
3
4

(5 row(s) affected)

UniqueId             Name                           Date                   Description            ThumbPath      
-------------------- ----------------------- ---------------------------------------------------- ----
10                   The Beatles Tour               1961-07-28 00:00:00    Another memory         NULL           
9                    60's Active                    1958-06-10 00:00:00    Another memory         NULL           
8                    Gorden Blair                   1954-06-10 00:00:00    Another memory         NULL           
6                    Memory                         1953-06-10 00:00:00    Another memory         NULL           
7                    Memory                         1953-06-10 00:00:00    Another memory         NULL           
                                                                   
(5 row(s) affected)

UniqueId             Name                            Date                    ThumbPath
-------------------- ------------------------------- ----------------------- --------------
4                    Memory                          1918-06-10 00:00:00     NULL
3                    Fred Fish                       1921-06-10 00:00:00     NULL
2                    Memory                          1946-06-10 00:00:00     NULL
5                    Memory 1947                     1947-06-10 00:00:00     NULL
6                    Memory                          1953-06-10 00:00:00     NULL

(5 row(s) affected)

UniqueId             Name                            Date                    ThumbPath
-------------------- ------------------------------- ----------------------- --------------
11                   The Beatles Refused Entry       1967-07-31 00:00:00     NULL
17                   Memory                          1976-06-10 00:00:00     NULL
18                   Memory                          1979-06-10 00:00:00     NULL
19                   Memory                          1983-06-10 00:00:00     NULL
16                   Memory                          1983-06-10 00:00:00     NULL

(5 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 05:08:07

[code]DECLARE @Sample TABLE
(
theID INT,
theName VARCHAR(200),
theDate DATETIME
)

INSERT @Sample
SELECT 10, 'B', '20090101' UNION ALL
SELECT 11, 'A', '20090201' UNION ALL
SELECT 20, 'D', '20090301' UNION ALL
SELECT 19, 'E', '20090401' UNION ALL
SELECT 17, 'P', '20081201' UNION ALL
SELECT 12, 'R', '20081101' UNION ALL
SELECT 30, 'G', '20081001' UNION ALL
SELECT 99, 'F', '20080901' UNION ALL
SELECT 35, 'C', '20070601' UNION ALL
SELECT 60, 'S', '20070701' UNION ALL
SELECT 40, 'Z', '20070801' UNION ALL
SELECT 50, 'L', '20070901' UNION ALL
SELECT 51, 'K', '20090801'

DECLARE @WantedID INT,
@theDate DATETIME

SELECT @WantedID = 12,
@theDate = '20081101'

DECLARE @Stage TABLE
(
theID INT,
theDate DATETIME
)

SET ROWCOUNT 5

INSERT @Stage
SELECT theID,
theDate
FROM @Sample
WHERE theDate < @theDate
ORDER BY DATEDIFF(DAY, theDate, @theDate)

INSERT @Stage
SELECT @WantedID,
@theDate

INSERT @Stage
SELECT theID,
theDate
FROM @Sample
WHERE theDate > @theDate
ORDER BY DATEDIFF(DAY, @theDate, theDate)

SET ROWCOUNT 0

SELECT *
FROM @Stage
ORDER BY theDate[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources