May 16, 2012 at 8:22 am
Hi
What I am trying to do is do a calculation based on the previous records calculated value.
What I though may work is not as think buffers prior.
B is the latest records, A is the previous record. the trick here is how does one comit the the row then use it in the next one, without a cursor
Sample Script
Update b
set b.run_daily_avg_consum = cast(a.run_daily_avg_consum as decimal(15,5)) *.7 + b.Daily_avg_Consumption *.3
From [Stage1W_Dim_Readings] a, [Stage1W_Dim_Readings] b
Where a.pod_no = b.pod_no and a.Read_Seq_Pod = b.Read_Seq_Pod-1
Sample Excel Dump attached. Please can some-one give some suggestions.
May 16, 2012 at 8:32 am
i think this might answer your question
Update b
set b.run_daily_avg_consum = cast(a.run_daily_avg_consum as decimal(15,5)) *.7 + b.Daily_avg_Consumption *.3
From [Stage1W_Dim_Readings] a inner join [Stage1W_Dim_Readings] b on
a.pod_no = b.pod_no and a.Read_Seq_Pod = b.Read_Seq_Pod-1
MVDBA
May 16, 2012 at 12:36 pm
Is these not the same?
May 16, 2012 at 12:40 pm
You actually need to use a left outer join and deal with a null value. It would help if you could post the CREATE TABLE statement, some sample data (insert into statments). This isn't something I can wirte off the top of my head, I need data to work with.
May 18, 2012 at 12:45 am
Found a solution:
--===== Declare the working variables
DECLARE @Pod varchar(10)
DECLARE @RDATotal varchar(18)
DECLARE @AccountRunningCount INT
--===== Update the running total and running count for this row using the "Quirky
-- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the
-- order of the clustered index.
UPDATE [Stage1W_Dim_Readings]
SET @RDATotal = run_daily_avg_consum = CASE
WHEN Pod_no = @Pod
THEN cast(@RDATotal as decimal(15,7))*0.7 + Daily_avg_Consumption * 0.3
ELSE Daily_avg_Consumption
END,
@Pod = Pod_no
FROM dbo.[Stage1W_Dim_Readings] WITH (TABLOCKX)
where Read_Seq_Pod is not null
OPTION (MAXDOP 1)
It works. The cluster index took some time but is working well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply