Pull column value from second to last row-CodePudding

I'm stuck in a loop of figuring out a game plan for this in SQL. Below is my sample data. I'm trying to create another column called "Starting Balance" which would be the amount in "Ending Balance" for the previous LINE. When I have that, I would only like to display where reason = count and forget about the rest.

I can't even fathom what approach to take and any advice would be appreciated.

Sample Data:

ITEM ID	ITEM	LAST UPDATED	REASON	ENDING BALANCE	LINE
123	Pencil	9/1/2020	Correction	400	1
123	Pencil	9/2/2020	Correction	450	2
123	Pencil	9/3/2020	Count	500	3

Expected Output:

ITEM ID	ITEM	LAST UPDATED	REASON	Starting Balance	ENDING BALANCE
123	Pencil	9/3/2020	Count	450	500

CodePudding user response：

I can't bugtest this, but maybe something like:


SELECT
  a.ITEM_ID,
  a.ITEM,
  a.LAST_UPDATED,
  "Count" AS REASON,
  b.ENDING_BALANCE AS Starting_Balance,
  a.ENDING_BALANCE AS ENDING_BALANCE,
FROM table a
LEFT JOIN table b
ON a.ITEM_ID = b.ITEM_ID, a.LINE = b.LINE   1

Note that we're joining two copies of the same table together here and labeling them a and b. No clue if you can do ONs like that, but you could also make the join table have a LINE 1 column which you then use to join.

CodePudding user response：

if "previous LINE" means the row with Lastupdated before current row:

select * from (
    select * , lag(ENDING_BALANCE,1,0) over (partition by ItemId order by LASTUPDATED) as Starting Balance
    from table
) t where t.REASON = 'count'