SQL Calculation Help Required - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Calculation Help Required

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2013-10-24 : 20:18:53

SQL Calculation Help Required

I have the following data / table of transactions I want to record in a temporary table.

The starting value and the end value are calculation values except for the starting value for line 1

.........Starting value....Movement....End Value

Trans 1........100............10...........90
Trans 2.........90............25...........65
Trans 3.........65............30...........35

Notes

The starting value 100 I will read from a starting position
The movement column is data that will be extracted from elsewhere
The end value is a simple calculation from starting value – movement

The question I have is how to get the end value from line 1 to be the starting value in line 2 and then the end value in line 2 to be the starting value in line 3 and so forth?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 03:04:09

using CTE
illustration


declare @t table
(TransName varchar(30),
Movement int
)
insert @t
values ('Trans 1',10),
('Trans 2',25),
('Trans 3',30)

DECLARE @StartValue int
SET @StartValue = 100

;With CTE1
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY TransName) AS Seq,*
FROM @t
),
CTE2 
AS
(
SELECT TransName,@StartValue AS StartValue,Movement,@StartValue - Movement AS EndValue,Seq
FROM CTE1
WHERE Seq=1
UNION ALL
SELECT c1.TransName,c2.EndValue,c1.Movement,c2.EndValue - c1.Movement,c1.Seq
FROM CTE2 c2
INNER JOIN CTE1 c1
ON c1.Seq = c2.Seq + 1
)

SELECT TransName,EndValue,Movement,EndValue
FROM CTE2
OPTION (MAXRECURSION 0)


ouput
---------------------------------------------
TransName	StartValue	Movement	EndValue
---------------------------------------------------------
Trans 1	        100	        10	        90
Trans 2	        90	        25	        65
Trans 3	        65	        30	        35

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 03:17:07

Using Quirly update method



declare @t table
(TransName varchar(30),
Movement int
)

insert @t
values ('Trans 1',10),
('Trans 2',25),
('Trans 3',30),
('Trans 4',15)

create  table #t
(TransName varchar(30),
StartValue int,
Movement int,
EndValue int
)
create clustered index idx_clust on #t(TransName asc)
insert #t (TransName,Movement)
SELECT  TransName,Movement
FROm  @t
DECLARE @StartValue int,@TransName varchar(30)
SET @StartValue = 100

UPDATE #t WITH (TABLOCK)
SET @StartValue = StartValue =@StartValue,
@StartValue = EndValue = @StartValue - Movement,
@TransName = TransName
OPTION (MAXDOP 1)

SELECT *
FROM #t 

DROP TABLE #T


output
----------------------------------------------------------
TransName	StartValue	Movement	EndValue
----------------------------------------------------------
Trans 1	        100	        10	        90
Trans 2	        90	        25	        65
Trans 3	        65	        30	        35
Trans 4	        35	        15	        20

Refer

http://visakhm.blogspot.in/2010/03/using-quirky-updates-to-develop-well.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-30 : 03:59:10

[code]
declare @t table
(TransName varchar(30),
Movement int
)
insert @t
values ('Trans 1',10),
('Trans 2',25),
('Trans 3',30)

DECLARE @StartValue int
SET @StartValue = 100

select
Transname,
StartValue = lag(EndValue, 1, @StartValue) over (order by transname),
Movement,
EndValue
from (
select
*,
EndValue = @StartValue - sum(movement) over (order by transname)
from @t
)src
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-30 : 04:12:45

quote:

Originally posted by waterduck


declare @t table
(TransName varchar(30),
Movement int
)
insert @t
values ('Trans 1',10),
('Trans 2',25),
('Trans 3',30)

DECLARE @StartValue int
SET @StartValue = 100

select 
	Transname,
	StartValue = lag(EndValue, 1, @StartValue) over (order by transname),
	Movement,
	EndValue
from (
	select
		*,
		EndValue = @StartValue - sum(movement) over (order by transname)
	from @t
	)src

Good suggestion
just a caveat that works only in SQL 2012 and above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources