Calculating just the difference showing third col

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 2008 Forums

Transact-SQL (2008)

Calculating just the difference showing third col

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-03 : 12:01:29

I have created the following sampls data it shows the third column populated based on the difference using column two.
Is it possible to create an update query which populates just the dirrence in column three?

I will have values for first two columns, use an update statement to calculate the difference and show it in column three(Diff_Ev_Value.

declare @Sample_Final table (ActivityID int, EV_Value float, Diff_EV_Value float)
insert @Sample_Final
select 1, 12.00, 12.00, union all
select 2, 22.00, 10.00 union all
select 7, 37.00, 15.00 union all
select 22, 47.00, 10.00 union all
select 23, 58.00, 11.00 union all
select 45, 70.00, 12.00 union all
select 46, 92.00, 22.00

select * from @Sample_Final

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 12:15:30

use apply operator.


UPDATE s
SET s.Diff_EV_Value = s.EV_Value - COALESCE(s1.EV_Value,0)
FROM @Sample_Final s
OUTER APPLY (SELECT TOP 1 EV_Value 
             WHERE ActivityID < s.ActivityID 
             ORDER BY ActivityID DESC) s1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-03 : 12:27:32

Visakh,

It is populating the same values like column two in third column: you can see i have executed the following:

declare @Sample_Final table (ActivityID int, EV_Value float, Diff_EV_Value float)
insert @Sample_Final
select 1, 12.00, 0 union all
select 2, 22.00, 0 union all
select 7, 37.00, 0 union all
select 22, 47.00, 0 union all
select 23, 58.00, 0 union all
select 45, 70.00, 0 union all
select 46, 92.00, 0

UPDATE s
SET s.Diff_EV_Value = s.EV_Value - COALESCE(s1.EV_Value,0)
FROM @Sample_Final s
OUTER APPLY (SELECT TOP 1 EV_Value
WHERE ActivityID < s.ActivityID
ORDER BY ActivityID DESC) s1

select * from @Sample_Final

I was expecting these values in column three: 12, 10, 15, 10, 11, 12, 22

Thank you very much for the helpful info.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-03 : 12:35:33

missed the FROM clause


UPDATE s SET s.Diff_EV_Value = s.EV_Value - COALESCE(s1.EV_Value,0)
FROM @Sample_Final s
OUTER APPLY (SELECT TOP 1 x.EV_Value
from   @sample_final x
WHERE x.ActivityID < s.ActivityID
ORDER BY x.ActivityID DESC) s1

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:01:06

ah...nice catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-03 : 14:41:57

Happy to pitch in - but you did all the heavy lifting (as usual)

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 11:09:49

Visakh,

This is working good but, if there is a value zero in between it is not working.

Once you execute teh following you can see second row difference value is showing as -12, i am loking for straight +12....
is there a way to handle zero values in the process, if the value is zero then just show the previous value. in this case it should be 12 onwards..

----------
declare @Sample_Final table (ActivityID int, EV_Value float, Diff_EV_Value float)
insert @Sample_Final
select 1, 12.00, 0 union all
select 2, 0.00, 0 union all
select 7, 37.00, 0 union all
select 22, 47.00, 0 union all
select 23, 58.00, 0 union all
select 45, 70.00, 0 union all
select 46, 92.00, 0

UPDATE s SET s.Diff_EV_Value = s.EV_Value - COALESCE(s1.EV_Value,0)
FROM @Sample_Final s
OUTER APPLY (SELECT TOP 1 x.EV_Value
from @sample_final x
WHERE x.ActivityID < s.ActivityID
ORDER BY x.ActivityID DESC) s1

select * from @Sample_Final

-------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:32:33

[code]
UPDATE s SET s.Diff_EV_Value = ABS(s.EV_Value - COALESCE(s1.EV_Value,0))
FROM @Sample_Final s
OUTER APPLY (SELECT TOP 1 x.EV_Value
from @sample_final x
WHERE x.ActivityID < s.ActivityID
ORDER BY x.ActivityID DESC) s1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 15:11:59

Visakh,

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:39:21

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources