Running Balance Calculation - PLEASE HELP

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 2005 Forums

Transact-SQL (2005)

Running Balance Calculation - PLEASE HELP

Author

Topic

Tjaard
Starting Member

11 Posts

Posted - 2008-06-23 : 09:52:12

Hi guys, I've really been struggling with this issue for quite a while and the solution still elludes me.
If anyone can help me I will worship you as a god for ever! [Wink]

The Scenario is as follows:
I have a table where several documents are linked to each other via a foreign key called ReconNum.
Basically this table is used to link Invoices, Credit Notes and Payments to each other.
Thus you get the following layout:

ReconNum LineID DocID DocType ReconAmount
111 0 101 Payment 20 000
111 1 202 Credit Note 12 0000
111 2 303 Payment 5500
111 3 404 Invoice 10 000
111 4 505 Credit Note 22500
111 5 606 Invoice 30 000
111 6 607 Invoice 20 000

What I need to do is assign values from each of the Payment/Credit Note documents to the Invoices. Thus getting the following result:

Inv# DocID DocType AmountApplied DocBal InvBal
404 101 Payment 10 000 10 000 0
606 101 Payment 10 000 0 20 000
606 202 Credit Note 12 000 0 8 000
606 303 Payment 5500 0 2 500
606 505 Credit Note 2500 20 000 0
607 505 Credit Note 20 000 0 0

I've come close, but it seems to be the old chicken or the egg problem.i.e. I need one field to calculate the other and vica-versa.
(Amount Applied, DocBal and Inv Bal are all dependant on each other)

NOTE: The only field I'm really interested in is the AmountApplied field, but amount applied depends on the DocBal and InvBal fields.

I need a running balance on both the Amount Due on the Invoice, and the Amount Availble on the document being assigned to the invoices. The problem is that I'm trhowing the data into a temporary table and cant use fields in the temp table for my calculation.
i.e. AppliedSum = if (Doc.Available amount on Doc < Invoice Balance) then Doc.Available Amount
elseif (Doc.Available amount on Doc > Invoice Balance) then Invoice Balance

Invoice Balance = Invoice Tot - sum(Applied Amounts)

Any help would be breatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 10:00:11

Can you explain how you will get values for Amount Applied,DocBal or InvBal?
i.e 10000, 10000 & 0

Tjaard
Starting Member

11 Posts

Posted - 2008-06-23 : 10:13:58

That's exactly my problem... ;)
They should theoretically be derived from the following:

--How to get the Applied Amount:
-- 1) Have you been used? (The document): Select sum(TP.ApplSum) from #Bal TP where TP.ReconNum = B.ReconNum and TP.LineSeq = B.LineSeq and TP.rownum < B.rownum
-- 2) What is your balance?: oBal = oAmount - (Step 1)
-- 3) What is the Invoice Balance?: iBal = iAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.Inv# = B.Inv#)
-- 4) Is your balance higher than Invoice Balance?: if oBal > iBal then iBal else oBal

--oBal = oAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.ReconNum = B.ReconNum and TP.LineSeq = B.LineSeq and TP.rownum < B.rownum)
--iBal = iAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.Inv# = B.Inv#

--ApplSum = oBal is oBal < iBal and iBal if oBal > iBal

As you will see our problem is that values oBal, iBal and Applied Sum are all inter dependant....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 10:18:58

quote:
Originally posted by Tjaard

That's exactly my problem... ;)
They should theoretically be derived from the following:

--How to get the Applied Amount:
-- 1) Have you been used? (The document): Select sum(TP.ApplSum) from #Bal TP where TP.ReconNum = B.ReconNum and TP.LineSeq = B.LineSeq and TP.rownum < B.rownum
-- 2) What is your balance?: oBal = oAmount - (Step 1)
-- 3) What is the Invoice Balance?: iBal = iAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.Inv# = B.Inv#)
-- 4) Is your balance higher than Invoice Balance?: if oBal > iBal then iBal else oBal

--oBal = oAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.ReconNum = B.ReconNum and TP.LineSeq = B.LineSeq and TP.rownum < B.rownum)
--iBal = iAmount - (Select sum(TP.ApplSum) from #Bal TP where TP.Inv# = B.Inv#

--ApplSum = oBal is oBal < iBal and iBal if oBal > iBal

As you will see our problem is that values oBal, iBal and Applied Sum are all inter dependant....

Thanks for explanation. However i cant distinguish whats oBal,iAmount,oAmount,... can you explain them with the help of your tables?

Tjaard
Starting Member

11 Posts

Posted - 2008-06-23 : 10:35:54

Yeah sorry visakh,
oBal = (DocBal) The available (running) balance of the specific document.i.e. If the DocTotal = $1000 and I've used $100, $200 and $300 respectively then the oBal values should be $900, $700, and $400.

iBal = (InvBal) The open balance of the invoice being satisfied. Thus if I've assigned a value of $100 to the invoice who's openning amount was $1000, then iBal = $900.

iAmount = The ReconAmount of a Document of type Invoice.This is the initial Invoice amount.(Invoice Total)

oAmount = The ReconAmount of a Document NOT of type Invoice.This is the initial document amount. (Document total)

ApplSum = The specific amount assigned to the invoice on that line. This amount will decrease both the iBal and oBal values.

I hope that clarifies the situation a bit. Thank you for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 10:40:54

quote:
Originally posted by Tjaard

Yeah sorry visakh,
oBal = (DocBal) The available (running) balance of the specific document.i.e. If the DocTotal = $1000 and I've used $100, $200 and $300 respectively then the oBal values should be $900, $700, and $400.

iBal = (InvBal) The open balance of the invoice being satisfied. Thus if I've assigned a value of $100 to the invoice who's openning amount was $1000, then iBal = $900.

iAmount = The ReconAmount of a Document of type Invoice.This is the initial Invoice amount.(Invoice Total)

oAmount = The ReconAmount of a Document NOT of type Invoice.This is the initial document amount. (Document total)

ApplSum = The specific amount assigned to the invoice on that line. This amount will decrease both the iBal and oBal values.

I hope that clarifies the situation a bit. Thank you for your help!

Please post you table structure with some data. It helps us lot more than the verbal explanations.

Tjaard
Starting Member

11 Posts

Posted - 2008-06-23 : 11:05:13

Table Structure:


USE [Lead_LLC]
GO
/****** Object:  Table [dbo].[ITR1]    Script Date: 06/23/2008 16:50:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ITR1](
	[ReconNum] [int] NOT NULL,
	[LineSeq] [int] NOT NULL,
	[ShortName] [nvarchar](15) NULL,
	[TransId] [int] NULL,
	[TransRowId] [int] NULL,
	[SrcObjTyp] [nvarchar](20) NULL,
	[SrcObjAbs] [nvarchar](11) NULL,
	[ReconSum] [numeric](19, 6) NULL,
	[ReconSumFC] [numeric](19, 6) NULL,
	[ReconSumSC] [numeric](19, 6) NULL,
	[FrgnCurr] [nvarchar](3) NULL,
	[SumMthCurr] [numeric](19, 6) NULL,
	[IsCredit] [char](1) NULL,
	[Account] [nvarchar](15) NULL,
	[CashDisSum] [numeric](19, 6) NULL,
	[WTSum] [numeric](19, 6) NULL,
	[WTSumFC] [numeric](19, 6) NULL,
	[WTSumSC] [numeric](19, 6) NULL,
	[ExpSum] [numeric](19, 6) NULL,
	[ExpSumFC] [numeric](19, 6) NULL,
	[ExpSumSC] [numeric](19, 6) NULL,
	[netBefDisc] [numeric](19, 6) NULL,
 CONSTRAINT [ITR1_PRIMARY] PRIMARY KEY CLUSTERED 
(
	[ReconNum] ASC,
	[LineSeq] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Following is the code to limit my result set correctly and place in temp table:

Select TR.* 
into #Other
from ITR1 TR where TR.SrcObjTyp <> 13 and
(Select Count(ReconNum) from ITR1 TP where TP.ReconNum = TR.ReconNum and TP.SrcObjTyp = 13) > 1 and
(Select Count(ReconNum) from ITR1 TP where TP.ReconNum = TR.ReconNum and TP.SrcObjTyp <> 13) > 1)


Select TR.* 
into #RInv
from ITR1 TR where TR.SrcObjTyp = 13 and
(Select Count(ReconNum) from ITR1 TP where TP.ReconNum = TR.ReconNum and TP.SrcObjTyp = 13) > 1 and
(Select Count(ReconNum) from ITR1 TP where TP.ReconNum = TR.ReconNum and TP.SrcObjTyp <> 13) > 1)

Select ROW_NUMBER() OVER (Partition BY O.ReconNum ORDER BY O.ReconNum) AS rownum,
O.ReconNum, O.LineSeq, O.ShortName, O.SrcObjAbs, O.SrcObjTyp,I.SrcObjAbs as Inv#,
oAmount = case
when O.IsCredit = 'D' then O.ReconSum
when O.IsCredit = 'C' then O.ReconSum * -1
end,
iAmount = I.ReconSum
into #Bal
from #Other O inner join #RInv I on O.ReconNum = I.ReconNum

Will supply Insert Code with next post

Tjaard
Starting Member

11 Posts

Posted - 2008-06-23 : 11:12:19

Test Data:

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,0
           ,'C0001'
           ,563
           ,0
           ,30
           ,563
           ,608.94
           ,'D'
          )

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,1
           ,'C0001'
           ,536
           ,1
           ,24
           ,100114
           ,23574.54
           ,'C'
          )

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,2
           ,'C0001'
           ,535
           ,1
           ,24
           ,100113
           ,7020
           ,'C'
          )

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,3
           ,'C0001'
           ,407
           ,0
           ,13
           ,112
           ,44460
           ,'D'
          )

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,4
           ,'C0001'
           ,274
           ,1
           ,24
           ,100041
           ,51000
           ,'C'
          )

INSERT INTO [Test].[dbo].[ITR1]
           ([ReconNum]
           ,[LineSeq]
           ,[ShortName]
           ,[TransId]
           ,[TransRowId]
           ,[SrcObjTyp]
           ,[SrcObjAbs]
           ,[ReconSum]
           ,[IsCredit]
           )
     VALUES
           (1
           ,5
           ,'C0001'
           ,77
           ,0
           ,13
           ,16
           ,36525.6
           ,'D'
          )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 12:23:38

so you want AppliedSum,InvBal & DocBal to be calculated together? or will you be precaclculating any of them before?

Tjaard
Starting Member

11 Posts

Posted - 2008-06-24 : 00:15:36

quote:
Originally posted by visakh16

so you want AppliedSum,InvBal & DocBal to be calculated together? or will you be precaclculating any of them before?

Hi again VisaKH16, the only value I'm actually interested in is the AppliedSum. But I believe one needs the InvBal and DocBal values to calculate the AppliedSum correctly. Thus, one has to calculate all 3 values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 01:03:16

quote:
Originally posted by Tjaard

quote:
Originally posted by visakh16

so you want AppliedSum,InvBal & DocBal to be calculated together? or will you be precaclculating any of them before?

Hi again VisaKH16, the only value I'm actually interested in is the AppliedSum. But I believe one needs the InvBal and DocBal values to calculate the AppliedSum correctly. Thus, one has to calculate all 3 values.

But you say InvBal & DocBal requires applied sum in their calculations as per the earlier post.I think there should be a way to precalculate any one quantity independent of the other.

Tjaard
Starting Member

11 Posts

Posted - 2008-06-24 : 01:25:50

quote:
Originally posted by visakh16

quote:
Originally posted by Tjaard

quote:
Originally posted by visakh16

so you want AppliedSum,InvBal & DocBal to be calculated together? or will you be precaclculating any of them before?

Hi again VisaKH16, the only value I'm actually interested in is the AppliedSum. But I believe one needs the InvBal and DocBal values to calculate the AppliedSum correctly. Thus, one has to calculate all 3 values.

But you say InvBal & DocBal requires applied sum in their calculations as per the earlier post.I think there should be a way to precalculate any one quantity independent of the other.

That's my whole problem... ;)
Calculating the one without using the other, not sure how to do it.

As stated earlier the values are comprised as follows:


InvBal = Invoice Total - sum(ApplSum)
DocBal = Document Total - sum(ApplSum)
ApplSum = if (InvBal > DocBal and DocBal > 0) then DocBal
          elseif (DocBal = 0) then 0
          elseif (InvBal < DocBal and DocBal > 0) then InvBal

If you can tell me how to get the ApplSum in another way I will be seriously impressed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 01:57:23

quote:
Originally posted by Tjaard

quote:
Originally posted by visakh16

quote:
Originally posted by Tjaard

quote:
Originally posted by visakh16

so you want AppliedSum,InvBal & DocBal to be calculated together? or will you be precaclculating any of them before?

Hi again VisaKH16, the only value I'm actually interested in is the AppliedSum. But I believe one needs the InvBal and DocBal values to calculate the AppliedSum correctly. Thus, one has to calculate all 3 values.

But you say InvBal & DocBal requires applied sum in their calculations as per the earlier post.I think there should be a way to precalculate any one quantity independent of the other.

That's my whole problem... ;)
Calculating the one without using the other, not sure how to do it.

As stated earlier the values are comprised as follows:

InvBal = Invoice Total - sum(ApplSum)
DocBal = Document Total - sum(ApplSum)
ApplSum = if (InvBal > DocBal and DocBal > 0) then DocBal
elseif (DocBal = 0) then 0
elseif (InvBal < DocBal and DocBal > 0) then InvBal

If you can tell me how to get the ApplSum in another way I will be seriously impressed.

what does your requirement suggest?

Tjaard
Starting Member

11 Posts

Posted - 2008-06-24 : 02:08:13

I need to assign amounts from Documents such as Credit Notes and Payments to Invoices untill those Invoices are satisfied. It's a Reconcilliation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 02:23:10

quote:
Originally posted by Tjaard

I need to assign amounts from Documents such as Credit Notes and Payments to Invoices untill those Invoices are satisfied. It's a Reconcilliation.

So i guess AppliedSum figures be derived from CreditNotes/Payment records of the postedtable?

Tjaard
Starting Member

11 Posts

Posted - 2008-06-24 : 02:33:10

Yes. The ReconSum is the TOTAL value applied to the Reconcilliation from the specific Document.

This value may be assigned to a single Invoice, or spread over various Invoices.

i.e. If I have a Credit Note Recon Amount of $1000, and on that same recon I have an Invoice of $2000 then I will assign the $1000 to the Invoice (Leaving an Invoice Balance of $1000, and a Credit Note Balance of $0)

If I take the same example, but the Invoice Amount is $500, then I will assign $500 from the Credit Note to the Invoice (Leaving an Invoice balance of $0) and thus have a balance of $500 on the credit note to assign to ANOTHER invoice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources