Compare last data and second last data sql server based on id

Question

How do I create a view to find the last 2 records for a company id and to display the last value along with the difference between the last and the second last?

Here is some example data:

CREATE TABLE #161689 (
Dt DATE NULL,
Value INTEGER NULL,
CompanyId INTEGER NULL
);

INSERT INTO #161689
VALUES
('12/01/2010', 10, 10),
('11/01/2010', 5, 10),
('10/01/2010', 2, 10),
('08/10/2010', 1, 10),
('12/01/2010', 7, 20),
('10/01/2010', 3, 20),
('09/01/2010', 2, 20),
('08/10/2010', 2, 20);

Here is the expected result:

Date - Value - CompanyId - Diff

12.1.2010 - 10 - 10 - 5

12.1.2010 - 7 - 20 - 4

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

You have what we call a "greatest-n-per-group" problem. Plenty of community members from this stackexchange wrote up generic solutions for that problem here.

One approach for your problem is to use the ROW_NUMBER() window function to assign a number to all of the rows and to pivot the results from columns into rows. One implementation is as follows:

SELECT 
  CompanyId
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) Last_Value
, MAX(CASE WHEN RN = 1 THEN Dt ELSE NULL END) Last_Date
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) - COALESCE(MAX(CASE WHEN RN = 2 THEN Value ELSE NULL END), 0) DIFF
FROM
(
    SELECT 
      CompanyId
    , Value
    , Dt
    , ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY Dt DESC) RN
    FROM #161689
) t1
GROUP BY CompanyId;

To break it down step by step, the t1 derived table returns the following results:

╔═══════════╦═══════╦════════════╦════╗
║ CompanyId ║ Value ║     Dt     ║ RN ║
╠═══════════╬═══════╬════════════╬════╣
║        10 ║    10 ║ 2010-12-01 ║  1 ║
║        10 ║     5 ║ 2010-11-01 ║  2 ║
║        10 ║     2 ║ 2010-10-01 ║  3 ║
║        10 ║     1 ║ 2010-08-10 ║  4 ║
║        20 ║     7 ║ 2010-12-01 ║  1 ║
║        20 ║     3 ║ 2010-10-01 ║  2 ║
║        20 ║     2 ║ 2010-09-01 ║  3 ║
║        20 ║     2 ║ 2010-08-10 ║  4 ║
╚═══════════╩═══════╩════════════╩════╝

You're interested in the rows that have an RN value of 1 or 2. You need to combine the relevant two rows for each CompanyId into a single row. This can be accomplished with GROUP BY. I use MAX to get the latest or second latest as needed. The use of MAX is a bit misleading, since there's always just one row that matches. However, I need to use some aggregate function because this is a GROUP BY query so MAX works fine.

The final results are:

╔═══════════╦════════════╦════════════╦══════╗
║ CompanyId ║ Last_Value ║ Last_Value ║ DIFF ║
╠═══════════╬════════════╬════════════╬══════╣
║        10 ║         10 ║ 2010-12-01 ║    5 ║
║        20 ║          7 ║ 2010-12-01 ║    4 ║
╚═══════════╩════════════╩════════════╩══════╝

score 1 · Answer 2 · answered Jan 20 '17 at 03:26

Assuming SQL Server 2012 or newer, you can use the LAG() function:

;WITH x AS 
(
  SELECT Dt, Value, CompanyId,
   rn = ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY Dt DESC),
   prev = LAG(Value,1) OVER (PARTITION BY CompanyId ORDER BY Dt)
  FROM #161689
)
SELECT Dt, Value, CompanyId, Diff = Value - prev
FROM x WHERE rn = 1;

You could also think about it the opposite way and use LEAD():

   prev = LEAD(Value,-1) OVER (PARTITION BY CompanyId ORDER BY Dt DESC)

(This way the OVER clause for LEAD and for ROW_NUMBER match.)

Compare last data and second last data sql server based on id

2 Answers2