Technical Article

Retrieve consecutive records from the table based the value difference

,

This code sniippet is used to fetch the consecutive records from the table which has a difference of more than 500.

Previously, we used to prefer cursors. I have tried an alternative for the cursor (CTE).

Thanks and Regards,

Venkatesan Prabu .J

create table Venkat_sample(name varchar(100),amount int)
insert into Venkat_sample values('deepak',2000)
insert into Venkat_sample values('vijeta',2200)
insert into Venkat_sample values('shruti',4000)
insert into Venkat_sample values('prink',5000)

select * from Venkat_sample


with cte as
(
select row_number() over (order by amount) as Row_Id ,name, amount from Venkat_sample
)
select name as emp_name,amount from 
(
select (a.amount-b.amount) as diff,a.[Name],a.amount from cte a inner join cte b on a.Row_Id=b.Row_Id+1
)t where diff>500

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating