How to display values of previous rows

sql, sql-server, sql-server-2008, t-sql

Solution

Final edit after talking on chat to get a final solution:

DECLARE @Result TABLE
(   
  TICKR_SYMB VARCHAR (15) NOT NULL
  ,fromReviewStatus char(10)
  ,toReviewStatus char(10)
  ,ReviewStatusChangeDate DATETIME
  ,fromRestrictionStatus char(10)
  ,toRestrictionStatus char(10)
  ,RestrictionStatusChangeDate DATETIME
  ,fromCoverageStatus char(10)
  ,toCoverageStatus char(10)
  ,CoverageStatusChangeDate DATETIME
  ,fromRating VARCHAR(20)
  ,toRating VARCHAR(20)
  ,RatingChangeDate DATETIME
)

/* Rating History */
;WITH DecisionsHistory AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY H.TICKR_SYMB ORDER BY H.[Rating Change Date]) AS Row
        ,H.TICKR_SYMB
        ,H.[to Rating] AS toRating
        ,H.[Rating Change Date]
    FROM tblTickerRatingHistory H
)
INSERT @Result
(
    TICKR_SYMB
    ,fromRating
    ,toRating
    ,RatingChangeDate
)
SELECT
     CurrentHistory.TICKR_SYMB
    ,LastHistory.toRating AS fromRating
    ,CurrentHistory.toRating
    ,CurrentHistory.[Rating Change Date]
FROM DecisionsHistory CurrentHistory
LEFT JOIN DecisionsHistory LastHistory
    ON LastHistory.Row = (CurrentHistory.Row - 1)
    AND LastHistory.TICKR_SYMB = CurrentHistory.TICKR_SYMB

/* ReviewStatus */
;WITH ReviewStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.ReviewStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.ReviewStatus AS ToReviewStatus
    ,H.ReviewStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.ReviewStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromReviewStatus = LastActiveHistory.toReviewStatus
  ,toReviewStatus = CurrentActiveHistory.toReviewStatus
  ,ReviewStatusChangeDate = CurrentActiveHistory.ReviewStatusChangeDate
FROM @Result R
LEFT JOIN ReviewStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN ReviewStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2

/* CoverageStatus */
;WITH CoverageStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.CoverageStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.CoverageStatus AS ToCoverageStatus
    ,H.CoverageStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.CoverageStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromCoverageStatus = LastActiveHistory.toCoverageStatus
  ,toCoverageStatus = CurrentActiveHistory.toCoverageStatus
  ,CoverageStatusChangeDate = CurrentActiveHistory.CoverageStatusChangeDate
FROM @Result R
LEFT JOIN CoverageStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN CoverageStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2

 /*RestrictionStatus */
;WITH RestrictionStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.RestrictionStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.RestrictionStatus AS ToRestrictionStatus
    ,H.RestrictionStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.RestrictionStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromRestrictionStatus = LastActiveHistory.toRestrictionStatus
  ,toRestrictionStatus = CurrentActiveHistory.toRestrictionStatus
  ,RestrictionStatusChangeDate = CurrentActiveHistory.RestrictionStatusChangeDate
FROM @Result R
LEFT JOIN RestrictionStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN RestrictionStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2


SELECT
  R1.TICKR_SYMB
  ,R1.fromCoverageStatus
  ,R1.toCoverageStatus
  ,R1.CoverageStatusChangeDate
  ,R1.fromReviewStatus
  ,R1.toReviewStatus
  ,R1.ReviewStatusChangeDate
  ,R1.fromRestrictionStatus
  ,R1.toRestrictionStatus
  ,R1.RestrictionStatusChangeDate
  ,R1.fromRating
  ,R1.toRating
  ,R1.RatingChangeDate
FROM @Result R1
ORDER BY TICKR_SYMB, RatingChangeDate

Problem

I have two tables, I am struggling to write a query that will generate the result I require. Table 1 ``` CREATE TABLE [Table 1]( [ID] [int] NOT NULL, [Active_Status] [char](1) NOT NULL, [Status Change Date] [date] NOT NULL ) INSERT INTO [Table 1] VALUES (1,'Y','2000-01-15') INSERT INTO [Table 1] VALUES (1,'N','2003-01-20') INSERT INTO [Table 1] VALUES (2,'N','2002-01-25') INSERT INTO [Table 1] VALUES (2,'Y','2003-01-15') INSERT INTO [Table 1] VALUES (2,'N','2010-01-20') INSERT INTO [Table 1] VALUES (3,'Y','2005-01-25') INSERT INTO [Table 1] VALUES (3,'Y','2007-01-20') INSERT INTO [Table 1] VALUES (3,'N','2011-01-15') ``` Table 2 ``` CREATE TABLE [Table 2]( [ID] [int] NOT NULL, [Decision] [varchar](4) NOT NULL, [Decision Change Date] [date] NOT NULL ) INSERT INTO [Table 2] VALUES (1,'BUY' ,'2000-05-15') INSERT INTO [Table 2] VALUES (1,'SELL','2010-05-20') INSERT INTO [Table 2] VALUES (1,'SELL','2012-05-25') INSERT INTO [Table 2] VALUES (2,'HOLD','2004-05-15') INSERT INTO [Table 2] VALUES (2,'BUY' ,'2011-05-10') INSERT INTO [Table 2] VALUES (3,'SELL','2008-05-15') INSERT INTO [Table 2] VALUES (3,'BUY' ,'2011-05-25') ``` My desired output To start I need to sort my result table by `ID` and `Decision Change Date`. Subsequently I need to look up the appropriate `Active_Status` for the corresponding `Decision Change Date`. Likewise I need to display the `Active_Status` and `Decision` for the previous period.

Original source