How to display values of previous rows

Question

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.

Answer 1

You can do this by using CROSS APPLY and LAG.

SELECT t2.ID,
       LAG(t1.Active_Status, 1, NULL) OVER (PARTITION BY t2.ID ORDER BY t2.[Decision Change Date]) AS [From Active Status],
       t1.Active_Status AS [To Active Status], t1.[Status Change Date] AS [Active Status Change Date],
       LAG(t2.Decision, 1, NULL) OVER (PARTITION BY t2.ID ORDER BY t2.[Decision Change Date]) AS [From Decision Status],
       t2.Decision AS [To Decision Status], t2.[Decision Change Date]
FROM [Table 2] t2
CROSS APPLY (SELECT TOP 1 *
             FROM [Table 1]
             WHERE ID = t2.ID AND [Status Change Date] < t2.[Decision Change Date]
             ORDER BY [Status Change Date] DESC) t1
ORDER BY t2.ID, [Decision Change Date]

Answer 2

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 

Answer 3

here's something that should get you started.

    create table t1 (
  id int, act char(1), scd date
  )

create table t2 (
  id int, decs varchar(4), dcd date
  )

insert into t1 values

( 1, 'y', '20000115'),
( 1, 'n', '20030120'),
( 2, 'n', '20020125'),
( 2, 'y', '20030115'),
( 2, 'n', '20100120'),
( 3,'y','20050125'),
( 3,'y','20070120'),
( 3,'n','20110115')

insert into t2 values

(1,'buy','20000515' ),
(1,'sell', '20100520' ),
(1,'sell', '20120525' ),
(2,'hold', '20040515'),
(2,'buy', '20110510' ),
(3,'sell', '20080515'),
(3,'buy','20110525' )

with decisions as (

  select row_number() over (partition by id order by dcd) as rn,
   id, decs, dcd from t2
  ),
activities as 
(
  select row_number() over (partition by id order by scd) as rn,
  id, act, scd from t1
  )

select dec_to.id, x.from_act, x.to_act, x.scd, x.from_act, x.to_act, x.scd as scd, dec_from.decs as from_dec, dec_to.decs as to_dec, dec_to.dcd from decisions dec_from
right outer join decisions dec_to on dec_from.id = dec_to.id and 
dec_to.rn = dec_from.rn + 1
outer apply (
  select top 1 act_to.id, act_from.act as from_act, act_to.act as to_act, act_to.scd
  from activities act_to
  left outer join activities as act_from
  on act_from.id = act_to.id and act_from.rn = act_to.rn - 1
  where act_to.id = dec_to.id and act_to.scd <= dec_to.dcd
  order by act_to.scd desc
) x  

order by dec_to.id, dec_to.rn

yeah it is ugly. probably won't perform well on large datasets without proper indexing. however your requirements are vague, and the rules you use on what rows goes where does not make a whole lot of sense.

The assumption here is that you want the "latest" activity change row thats on or before the decision date. this works nicely and produces the following

ID  FROM_ACT    TO_ACT  SCD     FROM_DEC    TO_DEC  DCD
1           y   2000-01-15          buy 2000-05-15
1   y       n   2003-01-20  buy     sell    2010-05-20
1   y       n   2003-01-20  sell        sell    2012-05-25
2   n       y   2003-01-15          hold    2004-05-15  
2   y       n   2010-01-20  hold        buy 2011-05-10
3   y       y   2007-01-20          sell    2008-05-15
3   y       n   2011-01-15  sell        buy 2011-05-25

You can play with it at SQLFiddle