December 1, 2008 at 1:28 pm
Here is part of a stored procedure that is creating a general ledger with Lawson data:
CREATE TABLE #nisse (RowNum INT IDENTITY(1,1) NOT NULL
,Company int NOT NULL
,Name nvarchar(50)
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,postingdate nvarchar(10) --datetime NOT NULL
,updatedate nvarchar(10) --datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(30,2) NOT NULL
,TotBegCredits decimal(30,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (24,2) NOT NULL
,credits decimal (24,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,AcctSubBegBal MONEY NOT NULL
,AcctSubEndBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)
insert into #nisse
(company
,Name
,account
,acct_unit
,sub_account
,fiscal_year
,postingdate
,updatedate
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,AcctSubBegBal
,AcctSubEndBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
)
Select company
,Name
,account
,acct_unit
,sub_account
,fiscal_year
,convert(varchar,posting_date,101) as postingdate
,convert(varchar,update_date,101) as Updatedate
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,0
,0
,0
,0
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
FROM @tmp_Details
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on #nisse (Account, Acct_unit, sub_account,Fiscal_year, TMONTH, postingdate)
--beginning balance
UPDATE #nisse
SET BegBal = TotBegDebits + TotBegCredits
FROM #nisse
--running balance
DECLARE @PrevRunBal2 MONEY
SET @PrevRunBal2 = 0
DECLARE @PrevAcct2 int
SET @PrevAcct2 = 0
DECLARE @PrevSubAcct2 smallint
SET @PrevSubAcct2 = 9999
DECLARE @PrevAcctUnit2 nvarchar(15)
SET @PrevAcctUnit2 = ''
--set the beginning balances for Running Balance field ddr 9/11/08
UPDATE #nisse
SET RunBal = BegBal
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
UPDATE #nisse
SET @PrevRunBal2 = RunBal = Case
WHEN
Account = @PrevAcct2
and acct_unit = @PrevAcctUnit2
and Sub_Account = @PrevSubAcct2
THEN @PrevRunBal2
+ debits + credits
ELSE BegBal + debits + credits
END,
@PrevAcct2 = account,
@PrevSubAcct2 = sub_account,
@PrevAcctUnit2 = acct_unit
FROM #nisse
------------------------------------------------------
Everything works above this line. They also want a beginning and ending balance based on account and sub-account (accounting unit is not a deciding factor). It seems like it gets out of order and so my next Beginning Balance code, based on different factors, doesn't work.
DECLARE @AcctSubBegBal MONEY
SET @AcctSubBegBal = 0
SET @PrevAcct2 = 0
SET @PrevSubAcct2 = 9999
--setting balance, beginning and ending, for account/sub-account changes
--it doesn't change for accounting unit and in fact should add up
--accounting units
-- beginning
UPDATE #nisse
SET @AcctSubBegBal = AcctSubBegBal = Case
WHEN
Account <> @PrevAcct2
or Sub_Account <> @PrevSubAcct2
THEN BegBal
ELSE 0
END,
@PrevAcct2 = account
,@PrevSubAcct2 = sub_account
FROM #nisse
December 1, 2008 at 1:49 pm
Duplicate post. No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic611653-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply