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 (eg. derived table in universe), 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:

ProductID ModifiedDate PreviousDate NextDate
101 2010-10-01 Null 2010-10-02
101 2010-10-02 2010-10-01 2010-10-04
101 2010-10-04 2010-10-02 Null
203 2010-10-01 Null 2010-10-03
203 2010-10-03 2010-10-01 2010-10-04
203 2010-10-04 2010-10-03 Null
Recent Related Posts
Femke Kooij is a dashboard and report designer, developer and educator specializing in SAP | BusinessObjects Dashboards (better known as Xcelsius) and Crystal Reports. She has also a lot of experience with other SAP | BusinessObjects toolings like, Web Intelligence (WebI), Information Design Tool (Universe Designer), BusinessObjects Data Services (BODS) and the overall server platform BusinessObjects Enterprise (Launchpad, CMC). She spends a lot of her time thinking about and sharing her knowledge of Xcelsius to others through her own blog. Lately she is exploring other visualization tools like Qlikview and Tableau. On the later she is also posting some experiences. Currently she is in the employment of Cognizant Technology Solutions. Her blog only contains her personal views, thoughts and opinions. It is not endorsed by Cognizant Technology Solutions nor does it constitute any official communication of Cognizant Technology Solutions.

This entry was posted in Miscelleneaous, SQL and tagged , , . Bookmark the permalink.

Comments are closed.