June 20, 2008 at 2:33 am
(Insert Statement and test data posted below)
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! 😉
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!
June 20, 2008 at 8:21 am
Is there a date or other sequence you can use for ordering this data? If so, then it becomes a relatively easy task for a running total calculation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 23, 2008 at 1:00 am
Hi GSquared,
Yes there is a Document Date as well as a "LineNum"
(The Table makes use of a composite key existing out of ReconNum & LineID)
But I still don't know how I'm gonna keep track of both the Document used balance, and the Invoice balance seeing as they are dependant on each other...
The only way I can think of is to be able to look at fields I constructed in the Temp table while building the temp table (i.e. the previous Applied amounts). And that is not allowed.
What ideas do you have?
Thank you very much for your reply.
June 23, 2008 at 4:57 am
As stated earlier, my main aim is to retrieve the Amount Applied by each document to specific Invoices.
The Applied amount should be calculated by asking the Dcoment being processed the following questions:
-- 1) Have you been used?:
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
The relevant Values consists out of the following:
--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 = if oBal < iBal then oBal
elseif oBal > iBal then iBal
June 23, 2008 at 9:19 am
Here is the base table i'm working from plus some test data:
USE [Test]
GO
/****** Object: Table [dbo].[ITR1] Script Date: 06/23/2008 17:03:06 ******/
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
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'
)
Initial Code to get Resultset from base 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply