SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restart runningtotal when predetermined value is reached


Restart runningtotal when predetermined value is reached

Author
Message
matak
matak
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 3946
Hi, Im trying to create a running total based on FieldToSum that will restart everytime runningTotal <= 25 and count the number of times this happens in runningCount.
This needs to be done for both forwards and backwards.
This is currently what i have inherited (made some changes based on articles here already which has helped).
There are approximately 40 million records this needs to be done for.
If you run the following code then that will give the answer im after albeit quite slowly.
Im after any sort of help/advice with this. Also this is my first post so if im missing any information please let me know.

Current code

IF OBJECT_ID('TempDB..#runTotal','U') IS NOT NULL
DROP TABLE #runTotal

CREATE TABLE #runTotal
(
runTotalID INT IDENTITY(1,1),
tableID int,
FieldToSum FLOAT,
runningTotal float,
runningCount int
)

IF OBJECT_ID('TempDB..#table','U') IS NOT NULL
DROP TABLE #table

CREATE TABLE #table
(
tableID int NOT NULL,
runningCount int not null,
orderCol int,
FieldToSum float
)

insert #table
select 1, 0, 1, 4.733415944 union all
select 2, 0, 8, 4.733415944 union all
select 3, 0, 6, 4.733415944 union all
select 4, 0, 19, 4.733415944 union all
select 5, 0, 14, 6.950871733 union all
select 6, 0, 3, 6.942781406 union all
select 7, 0, 2, 6.956403557 union all
select 8, 0, 20, 6.945977457 union all
select 9, 0, 11, 6.956403557 union all
select 10, 0, 13, 6.956403557 union all
select 11, 0, 4, 6.945977457 union all
select 12, 0, 10, 6.956403557 union all
select 13, 0, 17, 6.945977457 union all
select 14, 0, 12, 6.956403557 union all
select 15, 0, 15, 6.968313001 union all
select 16, 0, 5, 6.968313001 union all
select 17, 0, 7, 7.024537923 union all
select 18, 0, 18, 7.024537923 union all
select 19, 0, 16, 6.968313001 union all
select 20, 0, 9, 6.968313001

CREATE UNIQUE CLUSTERED INDEX [Idx1] ON #runTotal(runTotalID )

Declare @flag AS int=0
DECLARE @Counter As INT =1
declare @runningTotal as float
declare @anchor int
WHILE @Flag=0 BEGIN
IF (select COUNT(1) from #table where runningCount=0)<>0
BEGIN
TRUNCATE Table #runTotal
INSERT INTO #runTotal
SELECT tableID, FieldToSum, 0, 0 FROM #table where runningCount=0 ORDER BY orderCol ASC
set @runningTotal = 0
update #runTotal
set @runningTotal = runningTotal = @runningtotal + FieldToSum,
@anchor = runtotalid
from #runTotal WITH (TABLOCKX) OPTION (MAXDOP 1)
UPDATE #table
SET runningCount=@Counter from #table a inner join #runTotal b on a.tableID = b.tableID where b.runningTotal <= 25
END
ELSE
SET @FLAG=1
IF (select COUNT(1) from #table where runningCount=0)<>0
BEGIN
TRUNCATE Table #runTotal
INSERT INTO #runTotal
SELECT tableID, FieldToSum, 0, 0 FROM #table where runningCount=0 ORDER BY orderCol DESC
set @runningTotal = 0
update #runTotal
set @runningTotal = runningTotal = @runningtotal + FieldToSum,
@anchor = runtotalid
from #runTotal WITH (TABLOCKX) OPTION (MAXDOP 1)
UPDATE #table
SET runningCount=@Counter from #table a inner join #runTotal b on a.tableID = b.tableID where b.runningTotal <= 25
END
ELSE
SET @FLAG=1

SET @Counter=@Counter+1
END



laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
This process uses the 'Quirky Update' without the essential requirements being in place. As such it is unreliable & will almost certainly give incorrect results in the future (or may have already done so).

Quirky Update is where variable & column are updated together:

update #runTotal
set @runningTotal = runningTotal = @runningtotal + FieldToSum



See this:
http://www.sqlservercentral.com/articles/T-SQL/68467/
for more details - the list of requirements for Quirky Update are towards the end.

It may seem a bit complicated - but unfortunately it is! :-)
matak
matak
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 3946
I appreciate the help with my broken quirky update. I believed ive fixed that and edited my first post to reflect the new code.
Is there anything else anyone can recommend that could help ?
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 3317
You could try the following

I'm sure that this can be improved though and I don't know if it will scale very well as it is recursive.

IF OBJECT_ID('TempDB..#table','U') IS NOT NULL
DROP TABLE #table

CREATE TABLE #table
(
tableID int NOT NULL,
runningCount int not null,
orderCol int,
FieldToSum float
)

IF OBJECT_ID('TempDB..#table2','U') IS NOT NULL
DROP TABLE #table2

CREATE TABLE #table2
(
tableID int NOT NULL,
runningCount int not null,
orderCol int,
FieldToSum float
)

insert #table
select 1, 0, 1, 4.733415944 union all
select 2, 0, 8, 4.733415944 union all
select 3, 0, 6, 4.733415944 union all
select 4, 0, 19, 4.733415944 union all
select 5, 0, 14, 6.950871733 union all
select 6, 0, 3, 6.942781406 union all
select 7, 0, 2, 6.956403557 union all
select 8, 0, 20, 6.945977457 union all
select 9, 0, 11, 6.956403557 union all
select 10, 0, 13, 6.956403557 union all
select 11, 0, 4, 6.945977457 union all
select 12, 0, 10, 6.956403557 union all
select 13, 0, 17, 6.945977457 union all
select 14, 0, 12, 6.956403557 union all
select 15, 0, 15, 6.968313001 union all
select 16, 0, 5, 6.968313001 union all
select 17, 0, 7, 7.024537923 union all
select 18, 0, 18, 7.024537923 union all
select 19, 0, 16, 6.968313001 union all
select 20, 0, 9, 6.968313001

CREATE UNIQUE INDEX [Idx1] ON #table(orderCol)

;WITH
maxordercol as (SELECT MAX(orderCol) endorder FROM #table),
a AS (
SELECT TableID, orderCol, FieldToSum,
FieldToSum RunTotal,
1 runningCount,
'A' direction
FROM #table
WHERE orderCol = 1
UNION ALL
SELECT t.TableID, t.orderCol, t.FieldToSum,
case when t.FieldToSum + a.runTotal <= 25 then
t.FieldToSum + a.runTotal
else
t.FieldToSum
end RunTotal,
case when t.FieldToSum + a.runTotal <= 25 then
a.runningCount
else
a.runningCount + 1
end runningCount,
'A' direction
FROM a
inner join #table t on t.orderCol = a.orderCol + 1
),
b as (
SELECT TableID, orderCol, FieldToSum,
FieldToSum RunTotal,
1 runningCount,
'D' direction
FROM #table
WHERE orderCol = (select endorder from maxordercol)
UNION ALL
SELECT t.TableID, t.orderCol, t.FieldToSum,
case when t.FieldToSum + b.runTotal <= 25 then
t.FieldToSum + b.runTotal
else
t.FieldToSum
end RunTotal,
case when t.FieldToSum + b.runTotal <= 25 then
b.runningCount
else
b.runningCount + 1
end runningCount,
'D' direction
FROM b
inner join #table t on t.orderCol = b.orderCol - 1
),
c AS (
select tableid, runningCount, ordercol, fieldtosum
from (
select *, row_number() OVER (partition by tableid order by runningCount) seq
from (select * from a union all select * from b) x
) y
where y.seq = 1
)
INSERT INTO #table2
SELECT *
FROM c
OPTION (MAXRECURSION 0)

UPDATE #table
SET runningCount = b.runningCount
FROM #table a INNER JOIN #table2 b ON a.tableid = b.tableid



I tried it over 1200 records as well and the performance was 40ms vs 4200ms
matak
matak
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 3946
Thanks !!
I will give it a go and let you know how it performs over our dataset.
matak
matak
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 3946
I will try and explain a little better. Im trying to mark each row based on having a something_runtot up to or equal to 25 - it cant go over this number and i am required to use the marked rows elsewhere (this is where runningCount comes in).

Tables are NOT magnetic tapes than can be run forward and backwards; what dose that mean? 

My terminology was a little off in places Smile
It means I need something_runtot up to or equal to 50 and the current business logic requires half from ascending and half from descending. So i need to get a running total up to or equal to 25, order by orderCol ascending then mark the records and i need to do the same for order by orderCol descending.
So in my current code the first time through the loop it will give the value of 1 to all rows with a runningTotal up to or equal to 25 ordered by orderCol asc and a value of 1 to all rows with a runningTotal up to or equal to 25 ordered by orderCol desc. This needs to be repeated until no more rows are left and no rows are allowed to be used more than once.

Do you have a true sequence number for the something_values you want to total?

Yes i do but when i insert the values i use the identity column to hold the ordering. (Im also not too sure why IDENTITY shouldnt be used).

Can you keep changing it?

I can change it as much as necessary to get better performance, however id prefer not to change from a float currently as thats what the base table is using.


I will give your code a go and see what happens - im in a totally different timezone so it will be a while before i can test it.

As always if my explanations dont make sense please let me know and i will try to rewrite it.
Thanks for the help.
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 24076
matak (9/26/2012)
I will try and explain a little better. Im trying to mark each row based on having a something_runtot up to or equal to 25 - it cant go over this number and i am required to use the marked rows elsewhere (this is where runningCount comes in).

Tables are NOT magnetic tapes than can be run forward and backwards; what dose that mean? 

My terminology was a little off in places Smile
It means I need something_runtot up to or equal to 50 and the current business logic requires half from ascending and half from descending. So i need to get a running total up to or equal to 25, order by orderCol ascending then mark the records and i need to do the same for order by orderCol descending.
So in my current code the first time through the loop it will give the value of 1 to all rows with a runningTotal up to or equal to 25 ordered by orderCol asc and a value of 1 to all rows with a runningTotal up to or equal to 25 ordered by orderCol desc. This needs to be repeated until no more rows are left and no rows are allowed to be used more than once.

Do you have a true sequence number for the something_values you want to total?

Yes i do but when i insert the values i use the identity column to hold the ordering. (Im also not too sure why IDENTITY shouldnt be used).

Can you keep changing it?

I can change it as much as necessary to get better performance, however id prefer not to change from a float currently as thats what the base table is using.


I will give your code a go and see what happens - im in a totally different timezone so it will be a while before i can test it.

As always if my explanations dont make sense please let me know and i will try to rewrite it.
Thanks for the help.


Be aware that the SUM.. OVER(ORDER BY ...) sub-clause requires SQL Server 2012

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search