Putting id's in contiguous order and batches using a tally table.

Question

Post reply

Putting id's in contiguous order and batches using a tally table.

Steve Bergkamp

Old Hand

Points: 335
More actions
December 13, 2010 at 5:51 am

#232022

I hope you guys can help me, I’m stumped
This is on SQL SERVER 2000.
I want to process id’s in a contiguous fashion, i.e 1,2,3,4. However, my ids’ are not necessarily contiguous.
So when my ids’ are not contiguous, I may only process one record.
Given a set of id’s:
id
-----------
0
2
3
4
6
7
8
9
10
11
I want to process batches of up to X (X could be 5000) like this:
•Batch A = id 0
•Batch B = id 2,3,4,
•Batch 4 = id 6,7,8,9,10,11
I want to use a tally table to put the id’s in order, and to figure out where the breaks are.
Tally Table usage: http://www.sqlservercentral.com/articles/T-SQL/62867/
I’m a little perplexed on how to accomplish this.
I appreciate any help you can offer.
Thanks Steve

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'd recommend you use a grouping concept like

Id-ROW_NUMBER OVER(ORDER BY Id)

So you Batches would be -1,0 and 1.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

To find out where the breaks are, you just LEFT JOIN your tally table to your table of IDs, and find the values in your tally table corresponding to NULLs in your table of IDs. Is that what you were after?

John

Steve Bergkamp Old Hand Points: 335 More actions · Answer 3

Thank you for the reply. I forgot one crucial piece of information. This has to run on SQL Server 2000 so the Over syntax, doesn't work for me.

Steve B.

Steve Bergkamp Old Hand Points: 335 More actions · Answer 4

John Mitchell-245523 (12/13/2010)
To find out where the breaks are, you just LEFT JOIN your tally table to your table of IDs, and find the values in your tally table corresponding to NULLs in your table of IDs. Is that what you were after?
John

I hadn't thought of this DOH!!. I am working in batches of 5000 so I handn't populated the Tally table with numbers through MAX(ID).

So this would work.

Any ideas if my Tally doesn't include all the same ID's in my ID table ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Steve Bergkamp (12/13/2010)
Any ideas if my Tally doesn't include all the same ID's in my ID table ?

Just make sure it does. You could put some logic at the beginning to check that you have as many rows in your tally table as MAX(ID), and return an error if you don't. Or, if your IDs start from a number much greater than 1, you could join on IDs.ID - n = Tally.Number, for example, where n is a number similar to MIN(ID).

John

LutzM SSC Guru Points: 107049 More actions · Answer 6

Steve Bergkamp (12/13/2010)
Thank you for the reply. I forgot one crucial piece of information. This has to run on SQL Server 2000 so the Over syntax, doesn't work for me.
Steve B.

You didn't forget to mention it.

I overlooked when reading. Sorry about that! :blush:

Would it be an option to insert the data into a temp table with an identity column? (Would be the old-style equivalent of ROW_NUMBER)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Bergkamp Old Hand Points: 335 More actions · Answer 7

Thanks for the help. This is what I came up with.

declare @minid int, @maxid int, @tofar int -- Minid holder, maxid holder, first null holder

declare @numberList table (N int, TicketID int) -- ticket to process batch of ticket against

declare @@batchsize int

declare @@indexcolumnname sysname

select @@indexcolumnname='AssignmentID'

select @@batchsize = 5000

select @minid=min(TicketID) from archive_tickets where archive_tickets.indexcolumnname='AssignmentID' and TicketID>4850

insert @numberList

select top 5000 numbers.n, archive_tickets.TicketID

from numbers

left outer join archive_tickets

on archive_tickets.TicketID=numbers.n and archive_tickets.indexcolumnname='AssignmentID'

where n>=@minid

select top 1 @tofar=n from @numberList where TicketID is null

select @maxid=@tofar-1

if @tofar is null select @maxid=max(TicketID) from @numberList

if @maxid is null set @maxid=@minid

select @tofar ToFar,@minid MinID, @maxid MAXID, @minid+4999 as Target, @maxid-@minid as Actual

--Check

Select N as N from @numberList where TicketID is null

--select * from @numbers

LutzM SSC Guru Points: 107049 More actions · Answer 8

Here's an example of the concept I mentioned in my previous post:

CREATE TABLE #temp

(

id INT IDENTITY(1,1),

val INT,

grp AS val-id persisted

)

INSERT INTO #temp (val)

SELECT 0 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9

SELECT *

FROM #temp

Depending on the data volume adding an index might be a good idea...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]