April 8, 2009 at 1:45 am
Hello
I have a little problem getting performance working on a table DW_CustTrans. I need the figures for a cube i am building, to show a customers balance in a given period.
The table consists of the following fields
[dataareaid] [varchar](3) NOT NULL,
[accountnumber] [varchar](10) NOT NULL,
[transdate] [datetime] NULL,
[amount] [float] NULL,
[Balance_MST] [float] NULL
My index on the table is on dataareaid,accountnumber,transdate
There is at this moment approx 424000 records in the table.
an example of the data when transferred from my ERP could be
DataareaidAccountnumberTransdateamountBalance_MST
-------------------------------------------------------------------------------
de3400132007-05-16 00:00:00.000179,69NULL
de3400132007-08-09 00:00:00.000-179,69NULL
de3400132009-01-23 00:00:00.000-90,44NULL
de3400132009-02-01 00:00:00.00090,44NULL
The idea is that i want the Balance_MST to show the customers balance at any given time.
DataareaidAccountnumberTransdateamountBalance_MST
-------------------------------------------------------------------------------
de3400132007-05-16 00:00:00.000179.69-179.69
de3400132007-08-09 00:00:00.000-179.690.0
de3400132009-01-23 00:00:00.000-90.4390.43
de3400132009-02-01 00:00:00.00090.439NULL
My first (and only) test was to make a simple SQL update
Update DW_CustTrans
SET Balance_MST =
(Select sum(Amount) from DW_CustTrans C where c.dataareaid = DW_CustTrans.dataareaid and c.accountnumber = DW_CustTrans.accountnumber and DW_CustTrans.transdate < c.transdate)
But this takes abount 4.5 hours to update, so now i am stuck. I am not very good with MDX but if that is the right solution then you are welcome to add this as well. Or if you have fallen over the same question in here please let med know. i have searched for something alike but found nothing.
Thanks in advance
ABB
April 8, 2009 at 2:31 am
Hi abb,
One of the article by Jeff Moden
on Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5.
link http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
ARUN SAS
April 8, 2009 at 4:25 am
Thanks, it worked for me. i wrote a long ansver but it is gone.
ABB
April 8, 2009 at 4:25 am
YES YES, it is working, had to make some minor alterations due to an extra dimension (dataareaid)
Thanks a lot for the quick reply and the correct answer.
I went from 4.5 hours to 7 !!!! seconds, talk about an improvement. I added my alteration in case any can use it again
Thanks again, and specially thanks to Jeff Moden witch is the originator of the code
Kind regards
ABB
-- Original code from Jeff Moden alterated by ABB
DECLARE @PrevCompany varchar(3)
SET @PrevCompany = ''
DECLARE @PrevCompanyBalance MONEY --running total for each company
SET @PrevCompanyBalance = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID varchar(10) --The "account change detector"
SET @PrevAcctID = ''
UPDATE dbo.DW_CustTrans
SET --===== Running Total Company
@PrevCompanyBalance = CompanyBalance = CASE
WHEN dataareaid = @Prevcompany
THEN @PrevCompanyBalance + Amount
ELSE Amount
END,
--===== Account Running Total (Reset when account changes)
@PrevGrpBal = Balance_MST = CASE
WHEN accountnumber = @PrevAcctID
THEN @PrevGrpBal + Amount
ELSE Amount -- Restarts total at "0 + current amount"
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Account Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = CompanyCount = CASE
WHEN accountnumber = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account and Company change detection"
@PrevAcctID = accountnumber,
@prevCompany = dataareaid
FROM dbo.DW_CustTrans WITH (INDEX(IX_DW_CustTrans),TABLOCKX)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply