Find day difference of last two rows-CodePudding

What I'm trying to do is find the MAX date and do a datediff between the most recent date and the second to last date to create a single column for the difference in days. How do I get rid of the first two rows? I attempted to do a MAX by wrapping it another table, no luck.

Sample Data:

ITEM ID	ITEM	LAST UPDATED	REASON
123	Pencil	4/1/2020	Correction
123	Pencil	8/1/2020	Correction
123	Pencil	9/3/2020	Correction
456	Highlighter	5/1/2020	Correction
456	Highlighter	5/10/2020	Correction
789	Pen	10/1/2020	Correction
789	Pen	10/1/2020	Correction

Expected Output:

ITEM ID	ITEM	LAST UPDATED	REASON	Days Diff Since Last Correction
123	Pencil	9/3/2020	Correction	33
456	Highlighter	5/10/2020	Correction	9
789	Pen	10/20/2020	Correction	19

Here's what I've used so far:

SELECT 
       [Item_ID]
      ,[Item]
      ,[Last_Updated]
      ,[Reason]
      ,DATEDIFF(day,lag([Last_Updated],1) over(partition by [Item_ID] ORDER BY [Last_Updated] asc), [Last_Updated]) AS DAY_DIFF
  FROM [Table]

This is giving me the below:

Item_ID Item    Last_Updated    Reason  DAY_DIFF
123 Pencil  2020-04-01  Correction  NULL
123 Pencil  2020-08-01  Correction  122
123 Pencil  2020-09-03  Correction  33
456 Highlighter 2020-05-01  Correction  NULL
456 Highlighter 2020-05-10  Correction  9
789 Pen 2020-10-01  Correction  NULL
789 Pen 2020-10-20  Correction  19

CodePudding user response：

select t.* from(
SELECT 
       [Item_ID]
      ,[Item]
      ,[Last_Updated]
      ,[Reason]
      ,datediff(day, lag([Last_Updated],1,Last_Updated)over (partition by [Item_ID] order by [Last_Updated]),[Last_Updated])  as 'Difference Between Last Correction',
row_number() over (partition by [Item_ID] order by [Last_Updated] desc) as rn
  FROM [TABLE]
)t
where rn = 1;