Update columns based on other column values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Update columns based on other column values

Author

Topic

SujitGouda
Starting Member

3 Posts

Posted - 2015-03-12 : 23:00:40

I have below records coming in from source tables

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType and TranDesc column as IN/OUT respectively as shown below.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

Can anyone help me with this ??? This is very urgent. The order of the records coming in can be different

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 10:40:37

[code]
with cte as
(
select *, lag(amount) over(partition by prodname order by (select 1)) lag_amount
from yourtable
)

update cte
set TranType = case when amount > lag_amount and amount < lag_amount*1.05 then 'IN'
when amount < lag_amount and amount > lag_amount*0.95 then 'OUT'
else TranType
end
[/code]

SujitGouda
Starting Member

3 Posts

Posted - 2015-03-13 : 13:15:57

I just wanted to let you guys know that this is in SQL Server 2008.

The Lag function is not working in my database.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 13:28:34

you posted in the 2012 section. Hence the lag function

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 13:38:31

[code]
with cte as
(
select *, rn=ROW_NUMBER() over(partition by ProdName order by (select 1))
from yourtable
)

update cte
set TranType = case when cte.amount > cte2.amount and cte.amount < cte2.amount*1.05
then 'In'
when cte.amount < cte2.amount and cte.amount > cte2.amount*0.95
then 'Out'
else cte.trantype
end
from cte
join cte cte2
on cte.ProdName = cte2.ProdName and cte.rn = cte2.rn+1
;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources