T-SQL: Previous and Next Modified Date

Normally queries like this are not really solved in T-SQL; rather a front end UI like WebI is capable of navigating through the records.

However if you intend doing it in T-SQL, then here’s how we can retrieve the Next and Previous Records of a given record:

/* Create Sample Table */
DECLARE @TT table
(
ProductID int,
ModifiedDate datetime,
CategoryGroupName varchar(10)
)

/* Create Sample Data */
INSERT INTO @TT VALUES ( 101, ’2010-10-01', ‘AA’)
INSERT INTO @TT VALUES ( 203, ’2010-10-01', ‘AA’);
INSERT INTO @TT VALUES ( 305, ’2010-10-01', ‘AA’);
INSERT INTO @TT VALUES ( 101, ’2010-10-02', ‘BB’);
INSERT INTO @TT VALUES ( 203, ’2010-10-03', ‘BB’);
INSERT INTO @TT VALUES ( 634, ’2010-10-03', ‘BB’);
INSERT INTO @TT VALUES ( 101, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 203, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 305, ’2010-10-04', ‘CC’);
INSERT INTO @TT VALUES ( 634, ’2010-10-04', ‘CC’);

SELECT
Prod2.ProductID,
Prod2.ModifiedDate,
(SELECT MAX(ModifiedDate)
FROM @TT Prod1
WHERE     Prod1.ModifiedDate <  Prod2.ModifiedDate and Prod1.ProductID=Prod2.ProductID ) as PreviousModifiedDate,
(SELECT MIN(ModifiedDate)
FROM @TT Prod1
WHERE     Prod1.ModifiedDate >  Prod2.ModifiedDate and Prod1.ProductID=Prod2.ProductID) as NextModifiedDate
FROM @TT  Prod2
GROUP BY Prod2.ProductID, Prod2.ModifiedDate
ORDER BY 1,2

Result looks like:

View the original article here