Loop through table to group associated records

Question

Loop through table to group associated records

scarlam

SSC Veteran

Points: 210
More actions
October 24, 2012 at 7:51 pm

#393455

Hi All,
I have a table containing about 1mil records that i need to loop through, find associated records and insert in another table to create a file.
For example in the data sample below, if current row serial is one of (VC8, VC1,) then sum amount in rows below to equal amount of current amount value. then insert those associated records to a temp table
SerialPartIDAmount
VC8PC545 4,474.23
VC00586 225.00
VC00516 2,550.00
VC00421 40.00
VC9277 19.23
VC00815 1,620.00
vc00206 20.00
VC1133 348.76
VC9436 75.00
VC9534 273.76
VC1133 945.25
VC9854 945.25
VC1826 40.00
VC8147 40.00
VC1754 5,131.70
VC4PC545 3,101.70
VC9978 2,030.00
Any ideas on how to go about writing a script or stored procedure that can loop through this table. your help is greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To assist those who want to assist you. please post the table definition, sample data and required results from the sample data.(Click on the first link in my signature block for an article that contains the T-SQL code required to do same easily and quickly)

That said I took the liberty of "guessing" at your table design an using:

CREATE TABLE #T(Serial VARCHAR(4),PartID VARCHAR(9),Amt VARCHAR(10))

INSERT INTO #T

SELECT 'VC8', 'PC545', '4474.23' UNION ALL --note input modified

SELECT 'VC00', '586', '225.00' UNION ALL

SELECT 'VC00', '516', '2550.00' UNION ALL

SELECT 'VC00', '421', '40.00' UNION ALL

SELECT 'VC9', '277', '19.23' UNION ALL

SELECT 'VC00', '815', '1620.00' UNION ALL

SELECT 'vc00', '206', '20.00'

SELECT Serial,SUM(CONVERT(DECIMAL(10,2),Amt)) FROM #T GROUP BY Serial

Result:

Serial(No column name)

VC004455.00

VC84474.23

VC919.23

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

scarlam SSC Veteran Points: 210 More actions · Answer 2

I apologize about that. Glad you were able to make sense of my question. My table structure would follow along the lines of what you have posted.

How would i loop through the records in the source table, find the bebeginning of a group of records?

BriPan SSCrazy Points: 2061 More actions · Answer 3

Not getting your question properly

can you share some more data and also give result which you want.

give some more details in question.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

scarlam (10/25/2012)
I apologize about that. Glad you were able to make sense of my question. My table structure would follow along the lines of what you have posted.
How would i loop through the records in the source table, find the bebeginning of a group of records?

To assist you in finding "the beginning", what or how would you define beginning, by say a column that contains the datetime the row was entered, or does the table uses an identity column, or ? ? ? Here I can not guess what you would use or have available in that table.

And remember the most efficient method is a set based method (if available), NOT looping through a table.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

scarlam (10/24/2012)
Hi All,
I have a table containing about 1mil records that i need to loop through, find associated records and insert in another table to create a file.
For example in the data sample below, if current row serial is one of (VC8, VC1,) then sum amount in rows below to equal amount of current amount value. then insert those associated records to a temp table
SerialPartIDAmount
VC8PC545 4,474.23
VC00586 225.00
VC00516 2,550.00
VC00421 40.00
VC9277 19.23
VC00815 1,620.00
vc00206 20.00
VC1133 348.76
VC9436 75.00
VC9534 273.76
VC1133 945.25
VC9854 945.25
VC1826 40.00
VC8147 40.00
VC1754 5,131.70
VC4PC545 3,101.70
VC9978 2,030.00
Any ideas on how to go about writing a script or stored procedure that can loop through this table. your help is greatly appreciated.

You have nothing in the given data to guarantee the order of the rows. You cannot rely on a "naturally" occurring order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

scarlam (10/24/2012)
Any ideas on how to go about writing a script or stored procedure that can loop through this table.

You're a programmer, you work to a specification - or if you are unlucky, a verbal brief. You can do anything, provided that the explanation is correct, complete, and clear.

The specification you've provided us with is none of these. Nobody can do anything for you yet. Go back and fill in the missing information. Without it, folks can only guess.

Use this as your sample data and refer to it in your explanation:

;WITH SampleData (Serial, PartID, Amount) AS (

SELECT 'VC8', 'PC545', '4,474.23' UNION ALL

SELECT 'VC00', '586', '225.00' UNION ALL

SELECT 'VC00', '516', '2,550.00' UNION ALL

SELECT 'VC00', '421', '40.00' UNION ALL

SELECT 'VC9', '277', '19.23' UNION ALL

SELECT 'VC00', '815', '1,620.00' UNION ALL

SELECT 'vc00', '206', '20.00' UNION ALL

SELECT 'VC1', '133', '348.76' UNION ALL

SELECT 'VC9', '436', '75.00' UNION ALL

SELECT 'VC9', '534', '273.76' UNION ALL

SELECT 'VC1', '133', '945.25' UNION ALL

SELECT 'VC9', '854', '945.25' UNION ALL

SELECT 'VC1', '826', '40.00' UNION ALL

SELECT 'VC8', '147', '40.00' UNION ALL

SELECT 'VC1', '754', '5,131.70' UNION ALL

SELECT 'VC4', 'PC545', '3,101.70' UNION ALL

SELECT 'VC9', '978', '2,030.00')

SELECT Serial, PartID, Amount = CAST(REPLACE(Amount,',','') AS DECIMAL(8,2))

FROM SampleData

--WHERE Serial IN ('VC1','VC8')

ORDER BY Serial, PartID

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]