sql server - Self-Join table on previous date - Left Outer Join Version for Outer Apply

Question

MSSQL Server 2017

Table:

CREATE TABLE [T1] 
(
    ID decimal(28,6) NULL,
    S_DATE datetime  NULL,
    AMOUNT decimal(28,6) NULL,
);

INSERT INTO T1 (ID, S_DATE, AMOUNT)
VALUES (1, '2020-01-01', NULL),
       (1, '2020-01-02', 200),
       (1, '2020-01-03', 300),
       (1, '2020-01-04', 400),
       (1, '2020-01-05', 500);

Simple SELECT * from T1 prints:

+----+----------------------+--------+
| ID |        S_DATE        | AMOUNT |
+----+----------------------+--------+
|  1 | 2020-01-01T00:00:00Z | (null) |
|  1 | 2020-01-02T00:00:00Z | 200    |
|  1 | 2020-01-03T00:00:00Z | 300    |
|  1 | 2020-01-04T00:00:00Z | 400    |
|  1 | 2020-01-05T00:00:00Z | 500    |
+----+----------------------+--------+

I would like to achieve a self-join to match the amount of a date with that of the previous date in one row like this:

+----+----------------------+--------+------------------+
| ID |        S_DATE        | AMOUNT | AMOUNT_LAST_DATE |
+----+----------------------+--------+------------------+
|  1 | 2020-01-01T00:00:00Z | (null) | (null)           |
|  1 | 2020-01-02T00:00:00Z | 200    | (null)           |
|  1 | 2020-01-03T00:00:00Z | 300    | 200              |
|  1 | 2020-01-04T00:00:00Z | 400    | 300              |
|  1 | 2020-01-05T00:00:00Z | 500    | 400              |
+----+----------------------+--------+------------------+

Working outer apply:

SELECT t1.*,t2.Amount from t1
OUTER APPLY (SELECT TOP 1 t2.AMOUNT
             FROM t1 as t2
             WHERE T1.ID = T2.ID
             AND T2.S_DATE < T1.S_DATE
             ORDER BY T2.S_DATE DESC) t2;

What would the left out join look like?

SQL-FIDDLE: LINK

question from:https://stackoverflow.com/questions/65889335/self-join-table-on-previous-date-left-outer-join-version-for-outer-apply

Answer 1

Waitting for answers