1

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

  • Hi @wmasmaddy, it looks like you have an error in your data prep (there is no 13th month). I edited your post to change that row to December. It also wasn't clear to me exactly what you were asking. Do you require the last and the second last values to be different? Please edit my revision if I misunderstood your intent. Thanks! – Joe Obbish Jan 20 '17 at 2:42
  • Please tag with the version of SQL Server you are using. – Aaron Bertrand Jan 20 '17 at 3:19
1

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 ║
╚═══════════╩════════════╩════════════╩══════╝
1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.