running total help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

running total help

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-30 : 19:35:10

I'm trying to keep track of line item debit,credit,adjustments and credits and get the balance in each occurence. I'm trying to get to a rolling total if that makes sense.

For example, if I have 4 columns; InvNum,InvCrtDt, InvPstDt, Balance
(Balance is a calculation of Debit-credit-adjustments-payments).

InvNum,InvCrtDt, InvPstDt, Balance
1,1-14-2005,1-14-2005,115
1,1-14-2005,1-29-2005,-105
1,1-14-2005,1-29-2005,0
1,1-14-2005,2-11-2005,-10

What I'd like to do is have a view that selects InvNum,InvCrtDt, InvPstDt, Balance and also has a 'runningBal' and a marker(ZeroDte) ie '1'.

The rolling balance and marker would look like this in the in the above example;

InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte
1,1-14-2005,1-14-2005,115,115,Null
1,1-14-2005,1-29-2005,-105,10,Null
1,1-14-2005,1-29-2005,0,10,Null
1,1-14-2005,2-11-2005,-10,0,1

So, I need to keep track of the invoices current balance. When the running balance is <=5 then I need to put a 1 in the ZeroDte field.

Any help is much appreciated as always.

Job

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 22:42:38

[code]select *,
case when runningBalance = 0 then 1 else null end as ZeroDte
from
(
select *, (select sum (Balance) from balance x where x.InvPstDt <= b.InvPstDt) as runningBalance
from balance b
) as a[/code]

----------------------------------
'KH'

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-31 : 00:00:23

KH,

Thanks for the reply. I am unable to get the balance to be accurate by invoice...I get results like;
1009746,5/13/2002,5/13/2002,40,-24117.43,Null
1009746,5/13/2002,5/29/2002,-38.11,-71294.15,Null
1009804,5/13/2002,5/13/2002,339,-24117.43,Null
1009804,5/13/2002,5/29/2002,-310.77,-71294.15,Null

It should look like;
1009746,5/13/2002,5/13/2002,40,40,Null
1009746,5/13/2002,5/29/2002,-38.11,1.89,Null
1009804,5/13/2002,5/13/2002,339,339,Null
1009804,5/13/2002,5/29/2002,-310.77,28.23

It looks like I need to make the running balance applicable at the invoice level only, and it is summing ALL where the date is less than date..

Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 00:08:18

add the invoice no in the sub query

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 00:09:08

I missed out the InvNum in the sub query

(select sum (Balance) from balance x where x.InvNum = b.InvNum and x.InvPstDt <= b.InvPstDt)

----------------------------------
'KH'

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-31 : 01:07:22

That was it. Thanks so much for your help!

Job

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:11:37

If you use Reports, then make use of its Running Total feature

Madhivanan

Failing to plan is Planning to fail

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-31 : 09:57:41

I don't actually use Reports, However I do need to update a table with these results. I have to match on InvNum and InvCrtDt and the InvPstDt. But the thing with the InvPstDt is that I have to have a case statement that says if isdate(IEPstDte)=0 then BnkDpDte else IEPstdte as InvPstDt not sure how to do that relative to the join...so if it is a date join with that field, if it's not, then join the other field. InvNum is straightforward...

Job
Yak Posting Veteran

69 Posts

Posted - 2006-01-31 : 15:51:37

I've noticed another issue. I've changed the criteria to
'when runningBalance < 5 then 1' as opposed to
'when runningBalance = 0 then 1'.

Looking at the way the code is used, is it possible to only return the indicator 1 if this is the first time the criteria is met?

Using the data from earlier using different balances. Currently the code would give results such as:

InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte,Days
1,1-14-2005,1-14-2005,-15,-15,Null,Null,Null
1,1-14-2005,1-29-2005,85,70,Null,Null,Null
1,1-14-2005,1-29-2005,-66,4,1,15
1,1-14-2005,2-11-2005,0,4,1,28
1,1-14-2005,2-15-2005,-4,0,1,32

What I'd like it to show is:
InvNum,InvCrtDt, InvPstDt, Balance,runningBal,ZeroDte,Days
1,1-14-2005,1-14-2005,-15,-15,Null,Null,Null
1,1-14-2005,1-29-2005,85,70,Null,Null,Null
1,1-14-2005,1-29-2005,-66,4,1,15
1,1-14-2005,2-11-2005,0,4,Null,Null
1,1-14-2005,2-15-2005,-4,0,Null,Null

So, it would only put the marker the first time the <5 criteria is true.

Thanks again for all the help.

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-01 : 11:40:07

I've still been unable to get this 'first' occurance to put the 1 and leave the others. If anyone has any direction that would be great!

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources