My archive table looks like this:

    **ID / Ship_Date / AsOfDate / more cols**
    .. / Mar 5, 22 / Jan 10, 2021
    .. / Mar 5, 22 / Jan 9, 2021
    .. / Mar 1, 22 / Jan 8, 2021
    .. / Mar 5, 22 / Jan 7, 2021

Now I need to write a query to find out when did the Ship_Date most recently changed to Mar 5.

The answer would be Jan 9th.

I know how to use the “rank” feature and temp tables to figure this out.

But, I need to know how to determine this using a single query statement as I need to use such a single query as a sub query within another dynamically generated SQL.

Any help appreciated.

Thanks

CodePudding user response：

Given this sample data:

CREATE TABLE dbo.StuffAndThings
(
  ID       int, 
  ShipDate date, 
  AsOfDate date
);

INSERT dbo.StuffAndThings(ID, ShipDate, AsOfDate) VALUES
  (12, '20220305', '20210110'),   
  (12, '20220305', '20210109'),
  (12, '20220301', '20210108'), 
  (12, '20220305', '20210107');

I think this is the query you're after. First we use window functions to grab the previous row's ship date for each row, as well as the ship date from the most recent as of date. From that we can ask "what is the maximum change date where the ship date is not the same as the previous row's ship date?"

;WITH anchor AS
(
  SELECT ID, AsOfDate,
    PrevShipDate = LEAD(ShipDate,1) 
            OVER (PARTITION BY ID ORDER BY AsOfDate DESC),
    LastShipDate = FIRST_VALUE(ShipDate) 
            OVER (PARTITION BY ID ORDER BY AsOfDate DESC)
  FROM dbo.StuffAndThings
)
SELECT ID, LastShipDate, LastChangeDate = MAX(AsOfDate)
  FROM anchor AS a
  WHERE PrevShipDate <> LastShipDate
  GROUP BY ID, LastShipDate;

Output:

• Example db<>fiddle