May 19, 2011 at 6:04 pm
Hello,
I am at the point where I have been looking at this situation for too long and am most likely over thinking it, so am coming here for a fresh set of eyes and minds.
The situation that I have is I am wanting to group my records in multiples based on a running total of a column. So that when the running total of that column gets over a certain value (for example 6) the next group will be given another row number
create table list_1
(FieldA char(10) NULL
, fieldB char(10) NULL
, fieldC char(10) NULL
, Int1int NULL)
insert into list_1
VALUES ('ABC', 123, 'WHAT', 1)
insert into list_1
VALUEs ('DEF', 456, 'WHO', 4)
insert into list_1
VALUES ('GEH', 443, 'WHEN', 1)
insert into list_1
VALUES ('POL', 444, 'SOME', 1)
insert into list_1
VALUES ('CDF', 876, 'MORE', 2)
insert into list_1
VALUES ('RED', 23, 'PIE', 1)
insert into list_1
VALUES ('HGG', 444, 'WHAT', 1)
insert into list_1
VALUES ('ERE', 876, 'IF', 2)
insert into list_1
VALUES ('UIU', 23, 'TODAY', 1)
This should give you something like :
ABC 123 WHAT 1
DEF 456 WHO 4
GEH 443 WHEN 1
POL 444 SOME 1
CDF 876 MORE 2
RED 23 PIE 1
HGG 444 WHAT 1
ERE 876 IF 2
UIU 23 TODAY 1
What I am wanting to so is put an additional field on the end which represents a group/row number that resets once the sum of Int1 exceeds 6
For example
ABC 123 WHAT 1 1
DEF 456 WHO 4 1
GEH 443 WHEN 1 1
POL 444 SOME 1 2
CDF 876 MORE 2 2
RED 23 PIE 1 2
HGG 444 WHAT 1 2
ERE 876 IF 2 3
UIU 23 TODAY 1 3
Any ideas?
This is on SQL2008 R2 Std installation 🙂
Thanks in advance for your thoughts.
Cheers
Troy
May 19, 2011 at 7:43 pm
May 19, 2011 at 7:54 pm
How about this?
; WITH RecrusiveCTE AS
(
SELECT ID, FieldA , FieldB, fieldC , Int1 , Int1 AS RngTotal , 1 AS Ranker
FROM list_1
WHERE ID = 1
UNION ALL
SELECT OrigTable.ID , OrigTable.FieldA ,
OrigTable.fieldB , OrigTable.fieldC, OrigTable.Int1 ,
CASE WHEN (ReCTE.RngTotal + OrigTable.Int1) > 6 THEN OrigTable.Int1
ELSE ReCTE.RngTotal + OrigTable.Int1
END As RngTotal ,
CASE WHEN (ReCTE.RngTotal + OrigTable.Int1) <= 6 THEN ReCTE.Ranker
ELSE ReCTE.Ranker + 1
END As [RunningRank]
FROM RecrusiveCTE ReCTE
INNER JOIN list_1 OrigTable
ON ReCTE.ID + 1 = OrigTable.ID
)
SELECT * FROM RecrusiveCTE
NOT the best of methods; Jeff Moden has an excellent article on how to do this in another method ( known as Quirky Update[/url]) with an amazing speed.
{Edit : Added link to Jeff's wonderful article}
May 19, 2011 at 8:01 pm
You may wonder where i got the ID column from. From the purpose of running totals, if adopting my code, it requires an unique ID to each row; thus, i have added, without your permission, an IDENTITY column to the sourcetable ( list_1 ).. I sincerely hope that you have an ID column in your original table :pinch:
May 19, 2011 at 9:36 pm
thats awesome! I was trying to do recursion with the cte but my brain was just not kicking in and sorting it out for me. Thanks for that. This will do in the short term and I will look at identify a nicer way to get it accomplished.
Much appreciated!
May 20, 2011 at 12:01 am
TroyG (5/19/2011)
thats awesome! I was trying to do recursion with the cte but my brain was just not kicking in and sorting it out for me. Thanks for that. This will do in the short term and I will look at identify a nicer way to get it accomplished.Much appreciated!
You're welcome, Troy..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply