Calcuation based on previous row value

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)

Calcuation based on previous row value

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2014-02-12 : 22:38:58

Hi guys.

I'm trying to write a select statement where I need to do a calculation for a particular column and the result is dependent on the previous row value.

E.g. my select statement will return the following results


Order Number	 Amount	
------------------------
BAL		 $300	 	'This is the initial balance amount
1002		 $800	      	'Customer ordered $800 worth of goods
1002		-$800	    	'Customer paid $800.
1003		 $200		'Customer ordered $200 worth of goods
1003		-$500		'Customer paid $500.

But I need an additional column at the end calculating the Balance amount based on the previuos record


Order Number	Amount	Balance
---------------------------------
BAL			$300	
1002		 $800	$1,100
1002		-$800	$300
1003		 $200	$500
1003		-$500	$0

To simplify things, you can imagine my select statement is something like
SELECT OrderNumber, Amount, ____ AS 'Balance' FROM T_Order

The blank part is where I'm stuck. Any ideas?

Thanks in advance

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-13 : 01:33:27

hi,

to initial dataset, I added a column , an identity



;with aCTE
AS (
		select 1 as idNo, '0' as OrderNumber,0 as Amount,300 as Balance union all
		select 2,'1002',800,0 union all
		select 3,'1002',-800,0 union all
		select 4,'1003',200,0 union all
		select 5,'1003',-500,0 )

select C1.orderNumber,C1.Amount, coalesce(C1.Amount,0) + coalesce(C1.Balance,0) + coalesce(B.Balance,0) AS Balance
from  aCTE as C1
	outer apply
	(select Sum(Amount + Balance) as Balance
		from aCTE as C2
		where C2.idNo<C1.idNo
		) as B

the output:



orderNumber	Amount	Balance
0	0	300
1002	800	1100
1002	-800	300
1003	200	500
1003	-500	0

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-13 : 01:51:30

also you can use , in sql 2012, a new function LAG

I don't have 2012 - so I cann't test it



;with aCTE
AS (
		select 1 as idNo, '0' as OrderNumber,0 as Amount,300 as Balance union all
		select 2,'1002',800,0 union all
		select 3,'1002',-800,0 union all
		select 4,'1003',200,0 union all
		select 5,'1003',-500,0 )

, bCTE		 
AS
(
	select C1.orderNumber,C1.Amount, 
		coalesce(C1.Amount,0) + coalesce(C1.Balance,0) + LAG(coalesce(C1.Balance,0),1,0) OVER(ORDER by c1.idNo) as Balance	
	from  aCTE as C1
)	
	

select * from bCTE

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2014-02-13 : 01:53:48

Hi. I may have simplied my table.

I can't add Select 1 or SElect 2 to add a serial number to my records.

But I get your idea. Perhaps, some kind soul can help me figure out how to add a running serial number as the first column?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-13 : 02:01:24

use row_number()

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-13 : 02:05:21

[code]

;with aCTE
AS (
select 1 as idNo, '0' as OrderNumber,0 as Amount,300 as Balance union all
select 2,'1002',800,0 union all
select 3,'1002',-800,0 union all
select 4,'1003',200,0 union all
select 5,'1003',-500,0 )

,secondCTE
AS
(
select
ROW_NUMBER() OVER(ORDER by C1.OrderNumber) as idNo
,OrderNumber
,Amount
,Balance
from aCTE as C1
)

select C1.orderNumber,C1.Amount, coalesce(C1.Amount,0) + coalesce(C1.Balance,0) + coalesce(B.Balance,0) AS Balance
from secondCTE as C1
outer apply
(select Sum(Amount + Balance) as Balance
from secondCTE as C2
where C2.idNo<C1.idNo
) as B

[/code]

remark: you need a column to order the records/rows

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

hayashiryo
Starting Member

46 Posts

Posted - 2014-02-13 : 02:07:51

I got the serial number working using ROW_NUMBER function.

However, I couldn't get the OUTER APPLY to work.

Because my original select statement is a complicated union of statements.

E.g.


SELECT * FROM
(
     SELECT ORDER, AMOUNT FROM T_ORDER
     
     UNION
     
     SELECT ORDER, AMOUNT FROM T_PAYMENT

) AS RESULT

The above is a simplified version of my current statement. I hope you get my point.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-13 : 02:18:43

put your select in a CTE stmt



;with aCTE_initial
AS (
		select 1 as idNo, '0' as OrderNumber,0 as Amount,300 as Balance union all
		select 2,'1002',800,0 union all
		select 3,'1002',-800,0 union all
		select 4,'1003',200,0 union all
		select 5,'1003',-500,0 
)

,aCTE
AS (
	SELECT ORDER as [ORDER], AMOUNT as AMOUNT FROM T_ORDER
     
     UNION
     
     SELECT ORDER, AMOUNT FROM T_PAYMENT
)
,secondCTE
AS
(
	select 
			ROW_NUMBER() OVER(ORDER by C1.OrderNumber) as idNo
			,OrderNumber
			,Amount
			,Balance
	from aCTE as C1
)


select C1.orderNumber,C1.Amount, coalesce(C1.Amount,0) + coalesce(C1.Balance,0) + coalesce(B.Balance,0) AS Balance
from  secondCTE as C1
	outer apply
	(select Sum(Amount + Balance) as Balance
		from secondCTE as C2
		where C2.idNo<C1.idNo
		) as B

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources