Can u plz Query

1

image

I want to update the transaction colum in the table that has previous transaction(TansactionSequence minus one) from Y to E.
Ex:Update Transaction colum 5 and 10 to E
Note: 1. TansactionSequence with 1 is always Y, and can have multiple Transaction Sequence numbers.
2. SequenceNo is a primary Key

2 
Drop table if exists #s
go


Create table #s (
SequenceNo int primary key, Name char(3), CCNum varchar(32), TransSeq smallint, Transactions char(1))

insert into #s values 
(1, 'xxx', '111',1,'Y'),
(2, 'xxx', '111',2,'Y'),
(3, 'yyy', '222',1,'Y'),
(4, 'yyy', '222',2,'E'),
(5, 'yyy', '222',3,'Y'),
(6, 'zzz', '333',1,'Y'),
(7, 'zzz', '333',2,'Y'),
(8, 'bla', '444',1,'Y'),
(9, 'bla', '444',2,'E'),
(10, 'bla', '444',3,'Y')

Select * from #s

update s 
    set Transactions = s1.PreviousTransactions

  from #s S 
	join (Select *, Lag(transactions,1,0) over (partition by CCNum order by CCNum, TransSeq) as PreviousTransactions
			from  #s) s1
		on s.SequenceNo = s1.SequenceNo
		and s.Transactions = 'Y'
		and s1.PreviousTransactions = 'E'

Select * from #s
1 Like