Ending Balance = Beggining Balance

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)

Ending Balance = Beggining Balance

Author

Topic

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-28 : 01:45:23

sir good day,may i ask for your assistance
here is my table,i would like to ask for a code that will
pass the value endbal to beginbal of the nextrecord
and group by according to csdrkey.

CSHDRKey|TransDate|BeginBal|Debit|Credit | EndBal|DaysInterval |ADB
12967 2011-02-28 302.50 0.00 0.00 302.50 8 78.0645
12967 2011-03-08 0.00 0.00 302.50 0.00 23 0.00
15554 2011-02-28 100.00 0.00 0.00 100.00 8 25.8064
15554 2011-03-08 0.00 0.00 100.00 0.00 23 0.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 02:22:49

[code]
update t
set t.openbal=t1.endbal
from tbl t
cross apply (select top 1 endbal
from tbl
where CSHDRKey = t.CSHDRKey
and TransDate < t.TransDate
order by TransDate desc)t1
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 02:36:58

quote:


CSHDRKey TransDate  BeginBal  Debit  Credit  EndBal DaysInterval  ADB
12967    2011-02-28  302.50    0.00    0.00  302.50            8  78.0645
12967    2011-03-08    0.00    0.00  302.50    0.00           23   0.00
15554    2011-02-28  100.00    0.00    0.00  100.00            8  25.8064
15554    2011-03-08    0.00    0.00  100.00    0.00           23   0.00

How do you get the beginbal and endbal for those records in red ?

Do you need to update the endbal as well or is it a computed column ?

KH
[spoiler]Time is always against us[/spoiler]

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-28 : 02:53:37

quote:
Originally posted by khtan

quote:

CSHDRKey TransDate BeginBal Debit Credit EndBal DaysInterval ADB
12967 2011-02-28 302.50 0.00 0.00 302.50 8 78.0645
12967 2011-03-08 0.00 0.00 302.50 0.00 23 0.00
15554 2011-02-28 100.00 0.00 0.00 100.00 8 25.8064
15554 2011-03-08 0.00 0.00 100.00 0.00 23 0.00

How do you get the beginbal and endbal for those records in red ?

Do you need to update the endbal as well or is it a computed column ?

KH
[spoiler]Time is always against us[/spoiler]

---*** to attain the beggining balance i use this script ---***
SELECT @BeginBal = SUM(CASE Type WHEN 'C' THEN Amount ELSE Amount * - 1 END)
FROM ClientSavingsDTL WHERE CSHDrkey = @CSHDrKey
AND TransDate < @Enddate

SET @BeginBal = ISNULL(@BeginBal,0)

SELECT @MinDate = min(Transdate) FROM #TempSavings

--insert beginning balance
INSERT INTO #TempSavings VALUES(@CSHDrKey, dateadd(day,-1,@StartDate), @BeginBal,0,0, @BeginBal,0,0)

---***End

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-29 : 04:24:46

sir thank you for your help,i apply your code buy it only updates the 2 topmost record, here is my working table i want to update it
the begining balance from previous ending balance. thanks you in advance for your help.

----------------------------------------------------------------------
CSHDRKey |TransDate| BeginBal| Debit| Credit |EndBal
1008 12/31/2010 6762.48 0 0 6762.48
1008 1/6/2011 6762.48 0 120 6882.48
1008 1/10/2011 0 0 250 250
1008 1/25/2011 0 0 6035.48 6035.48
1008 1/28/2011 0 0 357 357
12967 2011-02-28 302.50 0 0 302.50
12967 2011-03-08 0 0 0 302.50
15554 2011-02-28 100.00 0 0 100.00
15554 2011-03-08 0 0 100.00 0

julius.delorino
Starting Member

29 Posts

Posted - 2012-05-29 : 06:22:10

quote:
Originally posted by visakh16

update t
set t.openbal=t1.endbal
from tbl t
cross apply (select top 1 endbal
from tbl
where CSHDRKey = t.CSHDRKey
and TransDate < t.TransDate
order by TransDate desc)t1

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

sir thank you for your response,i use your code but it only update the second row please see my latest post on this topic thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:13:03

for first row where should you get prev value from?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:30:54

quote:
Originally posted by julius.delorino

sir thank you for your help,i apply your code buy it only updates the 2 topmost record, here is my working table i want to update it
the begining balance from previous ending balance. thanks you in advance for your help.

----------------------------------------------------------------------
CSHDRKey |TransDate| BeginBal| Debit| Credit |EndBal
1008 12/31/2010 6762.48 0 0 6762.48
1008 1/6/2011 6762.48 0 120 6882.48
1008 1/10/2011 0 0 250 250
1008 1/25/2011 0 0 6035.48 6035.48
1008 1/28/2011 0 0 357 357
12967 2011-02-28 302.50 0 0 302.50
12967 2011-03-08 0 0 0 302.50
15554 2011-02-28 100.00 0 0 100.00
15554 2011-03-08 0 0 100.00 0

Is the endbal values available before hand?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources