-3

Table Definition

    CREATE TABLE TEST (
    KEY1 VARCHAR NULL
    Dt DATE NULL,
    ISSUE INTEGER NULL,
    );

Example Data

    INSERT INTO TEST
    VALUES
    (100, '9/01/2010', 5),
    (100, '14/01/2010', 10),
    (100, '13/01/2010', 9),
    (101, '08/10/2010', 12),
    (101,'25/01/2010', 23),
    (101,'01/01/2010', 3),
    (101,'20/01/2010', 22);

Data in Table

KEY1 | Dt         | ISSUE
---: | :--------- | ----:
 100 | 2010-01-09 |     5
 100 | 2010-01-14 |    10
 100 | 2010-01-13 |     9
 101 | 2010-10-08 |    12
 101 | 2010-01-25 |    23
 101 | 2010-01-01 |     3
 101 | 2010-01-20 |    22

Desired Operation

  • For the KEY1 column containing the values 100 the last ISSUE has a value of 10 and the n-1 ISSUE would be 9. We need the difference of the Dt column for these two values.
  • For the KEY1 column containing the values 101 the last ISSUE has a value of 23 and n-1 ISSUE is in this case 22. We need the difference of the Dt column for these two values.

Expected Output

 KEY1 | DIFF
------+------
  100 |    1
  101 |    5

db<>fiddle with example data

John K. N.
  • 17,649
  • 12
  • 51
  • 110
Arunava
  • 11
  • 1
  • 1
    Your data shows the last issue for KEY 101 is 12 on 8 October 2010, not 23 on 25 January 2010? Should 08/10/2010 be 08/01/2010? Even with that, it's hard to see why the DIFF for KEY 101 should be 5 (I think it should be 23 - 22 = 1). Also the INSERT table name doesn't match the CREATE TABLE statement. – Paul White Dec 29 '21 at 06:11
  • Hi there and welcome to the DBA.SE community. I have formatted your question and cleaned up the code to match SQL Server. If this isn't correct please add the correpsonding tag and I will adapt the db<>fiddle. Please [edit] your question if you can add any relevant details. Thanks. – John K. N. Jan 04 '22 at 10:11

1 Answers1

3

I think this is the code you need:

DROP TABLE IF EXISTS #TEST;

CREATE TABLE #TEST (
    KEY1 integer NOT NULL,
    Dt date NOT NULL,
    ISSUE integer NOT NULL,
);


INSERT #TEST
    (KEY1, Dt, ISSUE)
VALUES
    (100, CONVERT(date, '9/01/2010', 103), 5),
    (100, CONVERT(date, '14/01/2010', 103), 10),
    (100, CONVERT(date, '13/01/2010', 103), 9),
    (101, CONVERT(date, '08/01/2010', 103), 12),
    (101, CONVERT(date, '25/01/2010', 103), 23),
    (101, CONVERT(date, '01/01/2010', 103), 3),
    (101, CONVERT(date, '20/01/2010', 103), 22);


SELECT
    W.KEY1, DIFF = W.ISSUE - W.prev 
FROM 
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
            PARTITION BY T.KEY1
            ORDER BY T.Dt DESC),
        prev = ISNULL(LAST_VALUE(T.ISSUE) OVER (
            PARTITION BY T.KEY1
            ORDER BY T.Dt DESC
            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 0)
    FROM #TEST AS T
) AS W
WHERE
    W.rn = 1;

Online demo

Paul White
  • 83,961
  • 28
  • 402
  • 634