Post reply

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

  • 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://qa.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

  • December 13, 2010 at 5:56 am

    #1261639

    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]

  • December 13, 2010 at 6:06 am

    #1261647

    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

  • December 13, 2010 at 6:26 am

    #1261656

    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.

  • December 13, 2010 at 6:29 am

    #1261658

    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 ?

  • December 13, 2010 at 6:38 am

    #1261662

    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

  • December 13, 2010 at 7:48 am

    #1261709

    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]

  • December 13, 2010 at 3:17 pm

    #1261957

    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

  • December 13, 2010 at 4:23 pm

    #1261980

    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]

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

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