question

sujitgouda avatar image
sujitgouda asked

Update columns based on other column values in same data set

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
sqlserver-2012
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How do you determine which is IN and which is OUT? Can you get more than two records per ProdName? You've tagged SQL2005 and 2012 - which is it?
0 Likes 0 ·
Its Sql Server 2012. Trantype A should be updated to IN TranType B should be updated to OUT
0 Likes 0 ·
Also, you get more we can get more than 1 record per ProdName but need to specifically target the once with scenarios that are satisfied by Amount column condition.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
I'm still not absolutely clear, but from what you've said, this should get you somewhere near. It uses 2012 specific commands, Lag and Lead. They allow you to look forward and backward through a set to retrieve values from prior or subsequent records. More here: https://msdn.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx https://msdn.microsoft.com/en-us/library/hh213125%28v=sql.110%29.aspx Note that I've assumed that all records are paired (i.e. 1 A record, 1 S record) and that you have some value in your table to be able to group these into pairs. In my example below, this is the OrderId column. It isn't clear what you want to do with pairs that don't meet your criteria (i.e. value difference is > +/-5%), so I've just marked these as N/A Neither is it clear which value should be compared to get the 5%. I've assumed that the difference between the paired values should be within 5% of the row value, regardless of if it's an A or S record. create table #orders (OrderId int, ProdName varchar(5), Amount int, TranType char(1)) go insert #orders values (1,'P1',100,'A') , (1,'P1',105,'S') , (2,'P2',200,'A') , (2,'P2',205,'S') go -- use lag and lead -- just show the effect of using lag and lead first select * , previous = lag(Amount,1,null) over (partition by OrderId order by TranType) , subsequent = lead(Amount,1,null) over (partition by OrderId order by TranType) from #orders -- do the calcuation -- this cte cascade is not explicitly required, but makes the logic a bit simpler to see ; with cte as ( select * , previous = lag(Amount,1,null) over (partition by OrderId order by TranType) , subsequent = lead(Amount,1,null) over (partition by OrderId order by TranType) from #orders ) , cte2 as ( select * , abs_diff = abs(Amount - coalesce(previous,subsequent)) from cte ) , cte3 as ( select * -- avoid div by 0 with case , pct_diff = cast(case when abs_diff = 0 then 0 else cast(abs_diff as decimal(6,3)) / cast(Amount as decimal(6,3)) end as decimal(6,3)) from cte2 ) select * , new_trantype = case when pct_diff <= 0.050 then case TranType when 'A' then 'IN' else 'OUT' end else 'N/A' end from cte3 Just for fun, here's another way to do it, using windowed averages. You might run into rounding errors though. -- use windowed average ; with cte as ( select * , avg_for_order = avg(cast(Amount as decimal(12,6))) over (partition by OrderId) from #orders ) , cte2 as ( select * , abs_diff = abs((Amount - avg_for_order) * 2) from cte ) , cte3 as ( select * -- avoid div by 0 with case , pct_diff = cast(case when abs_diff = 0 then 0 else cast(abs_diff as decimal(6,3)) / cast(Amount as decimal(6,3)) end as decimal(6,3)) from cte2 ) select * , new_trantype = case when pct_diff <= 0.050 then case TranType when 'A' then 'IN' else 'OUT' end else 'N/A' end from cte3 They evaluate to different plans, so depending on your environment, one approach may perform better than the other.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sujitgouda avatar image
sujitgouda answered
Hi iainrobertson, Sorry for the delay in reply, I had to go for some days and the issue still persists. Earlier I was not able to describe the problem correctly. Please find a detailed description and the Image file of the data, that I am working on. Requirements: 1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display those set of trades. 2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange Out". Server: SQL Server 2008 ![alt text][1] [1]: /storage/temp/2315-fact-table.png,Hi iainrobertson, Sorry for the delay in reply, I had to go for some days and the issue still persists. Earlier I was not able to describe the problem correctly. Please find a detailed description and the Image file of the data, that I am working on. Requirements: 1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5% or equal, then display those set of trades. 2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange Out". Server: SQL Server 2008![alt text][1] [1]: /storage/temp/2314-fact-table.png

fact-table.png (91.3 KiB)
fact-table.png (88.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.