temp table losing its order-update won't work

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)

temp table losing its order-update won't work

Author

Topic

drhoades32
Starting Member

8 Posts

Posted - 2008-12-01 : 15:39:51

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

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

This isn't working--it appears the temp table has lost the index ordering. Any thoughts or suggestions?

I left out the other ending balance. I think I can get it if I can get this other Beginning Balance to work. It's like this balance would be the first row in a Crystal report, for each account/sub-account combination, then each accounting unit within each of those combinations would have it's own beginning and running balances (done and working).

It ends like this:
SELECT company,name, account,acct_unit, sub_account
,debits, credits, BegBal, RunBal
,AcctSubBegBal, AcctSubEndBal
, postingdate, Updatedate
,startdate, enddate,fiscal_year, tmonth
,SystemCode, JE
,TransDescr, acct_unitname
,reference, convert(nvarchar(8),Account) as AcctSort
FROM #nisse
ORDER BY Company
, account
,acct_unit
,sub_account
,fiscal_year
,tmonth
,postingdate

drhoades32

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-12-01 : 17:34:44

We had a similar issue. A proc using a temp table worked fine in SQL 2000, but the way SQL 2005 arranges it's ordering of temp tables is different to 2000.
We overcame it by using a real table with a clustered index and then just truncating and re-populating that within the proc, the orders of the records then remain consistent.
Don't know if this is useful for your scenario though.

Regards
David

drhoades32
Starting Member

8 Posts

Posted - 2008-12-01 : 17:41:11

I'm trying to avoid that. There will be multiple users from various "companies" running the report so I'd have to come up with unique table names. Doable, but I'd really like to avoid it.

drhoades32

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:03:34

If you're using sql 2005, you can find out running balance without using above type of update. can you post some sample data to illustrate how you want to calculate the running total with expected output. then i'll show you code to calaculate the running figures.

drhoades32
Starting Member

8 Posts

Posted - 2008-12-08 : 17:53:22

CREATE TABLE #nisse ( RowNum INT IDENTITY(1,1) NOT NULL
,Company int NOT NULL
,Name nvarchar(50) NOT NULL
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,postingdate nvarchar(10) NOT NULL
,TMONTH int NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,AcctBegBal MONEY NOT NULL
,AcctEndBal MONEY NOT NULL
,Debits MONEY NOT NULL
,Credits MONEY NOT NULL
,TotBegDebits MONEY NOT NULL
,TotBegCredits MONEY NOT NULL
)

insert into #nisse values(9480, '', 2008,10042, '1481000', 1, '2008-01-01',1,0,0,0,0,0,-64278.47, 25000.00, -2300.00)
insert into #nisse values(9480, '', 2008,10042, '1481000', 1, '2008-01-01',1,0,0,0,0,2820.01,0, 25000.00, -2300.00)
insert into #nisse values(9480, '', 2008,10042, '1481000', 2, '2008-02-01',1,0,0,0,0,1450.00,0, 27820.01,-66578.47)
insert into #nisse values(9480, '', 2008,10042, '1482000', 1, '2008-01-15',1,0,0,0,0,50.00,0, 2000.00, -4000.00)
insert into #nisse values(9480, '', 2008,10043, '1482000', 2, '2008-02-01',1,0,0,0,0,0,-250.00, 2050.00,-4000.00)
insert into #nisse values(9480, '', 2008,10043, '1482000', 2, '2008-02-01',1,0,0,0,0,0,-250.00, 2050.00, -4000.00)

ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (Fiscal_year, TMonth, Account,Acct_unit, sub_account,Postingdate,RowNum)

UPDATE #nisse
SET BegBal = TotBegDebits + TotBegCredits
FROM #nisse

select * from #nisse

I need the Beginning Balance to accumulate for every account, so it needs to add the beginning balance when the Acct_unit or sub_account changes, but not when the month changes. Any assistance would be appreciated

drhoades32

drhoades32
Starting Member

8 Posts

Posted - 2008-12-08 : 18:00:44

Sorry, my brain's tired. I need a different Account Beginning Balance that adds the Beginning Balance for each account when the acct_unit or sub_account changes, but not when the month changes.

drhoades32

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 18:17:01

[code]CREATE TABLE #Nisse
(
RowNum INT IDENTITY(1,1) NOT NULL,
Company int NOT NULL,
Name nvarchar(50) NOT NULL,
Fiscal_year int NOT NULL,
Acct_unit nvarchar(15) NOT NULL,
Account int NOT NULL,
sub_account int NOT NULL,
postingdate nvarchar(10) NOT NULL,
TMONTH int NOT NULL,
BegBal MONEY NOT NULL,
RunBal MONEY NOT NULL,
AcctBegBal MONEY NOT NULL,
AcctEndBal MONEY NOT NULL,
Debits MONEY NOT NULL,
Credits MONEY NOT NULL,
TotBegDebits MONEY NOT NULL,
TotBegCredits MONEY NOT NULL
)

INSERT #Nisse
SELECT 9480, '', 2008, 10042, '1481000', 1, '2008-01-01', 1, 0, 0, 0, 0, 0 , -64278.47, 25000.00, - 2300.00 UNION ALL
SELECT 9480, '', 2008, 10042, '1481000', 1, '2008-01-01', 1, 0, 0, 0, 0, 2820.01, 0 , 25000.00, - 2300.00 UNION ALL
SELECT 9480, '', 2008, 10042, '1481000', 2, '2008-02-01', 1, 0, 0, 0, 0, 1450.00, 0 , 27820.01, -66578.47 UNION ALL
SELECT 9480, '', 2008, 10042, '1482000', 1, '2008-01-15', 1, 0, 0, 0, 0, 50.00, 0 , 2000.00, - 4000.00 UNION ALL
SELECT 9480, '', 2008, 10043, '1482000', 2, '2008-02-01', 1, 0, 0, 0, 0, 0 , - 250.00, 2050.00, - 4000.00 UNION ALL
SELECT 9480, '', 2008, 10043, '1482000', 2, '2008-02-01', 1, 0, 0, 0, 0, 0 , - 250.00, 2050.00, - 4000.00

ALTER TABLE #Nisse ADD PRIMARY KEY CLUSTERED (Fiscal_year, TMonth, Account, Acct_unit, sub_account, Postingdate, RowNum)

DECLARE @Acct nvarchar(15),
@sub int,
@begbal MONEY

UPDATE #Nisse
SET @begbal = BegBal = CASE
WHEN @acct IS NULL OR @sub IS NULL THEN TotBegDebits + TotBegCredits
WHEN @acct <> acct_unit OR @sub <> sub_account THEN TotBegDebits + TotBegCredits
ELSE @begbal + TotBegDebits + TotBegCredits
END,
@acct = acct_unit,
@sub = sub_account

SELECT *
FROM #Nisse

DROP TABLE #Nisse[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources