Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Unanswered: Running Balance and Temp table calc- PLEASE HELP

    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:

    Code:
    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:

    Code:
    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!

  2. #2
    Join Date
    Jun 2008
    Posts
    6
    Table Create:

    Code:
    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:
    Code:
    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'
              )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •