Compare and Update

[dbinfoweb]

Hello, This is the scenario. I have a table with saleperson, sale, and paid and some other columns. one saleperson may have multiple records in this table. What I want to do is to compare the paid and sale column. If any of the records for that saleperson with sale > paid then I will update a column with not paid for all the records for
that saleperson and vise versa, if any of the records for that saleperson with sale < paid column I will update a column with paid. I am just wondering if anyone has had this solution for scenario like this. Thanks in advance.

 

[ClaireHCM]

create table sales(saleperson varchar(10),sale int,paid int,record varchar(10))
go
insert into sales select 'Mary',6,4,null
insert into sales select 'John',2,4,null
insert into sales select 'John',3,4,null
insert into sales select 'John',5,4,null
insert into sales select 'Sunny',5,4,null
insert into sales select 'Mike',1,5,null
insert into sales select 'Mike',3,5,null
go

select saleperson,sale,paid,
(case when sale>paid then 'not paid' when sale<paid then 'paid' end)as record
from sales
go