SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Calculation Help Required
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KingCarlos
Yak Posting Veteran

Australia
74 Posts

Posted - 10/24/2013 :  20:18:53  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 10/25/2013 :  03:04:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/25/2013 :  03:17:07  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 10/30/2013 :  03:59:10  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  04:12:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000