Insert sequence number

  • I need help to code to add sequence number.

    One claim number may have various max sequence number.

    For example,

    1) Claim number: 111 max sequence: 17

    2) Claim number: 222 max sequence: 38

    For 1) I need code to insert 18,19,20

    For 2) I need code to insert 39,40

    That is, based on max sequence number, inserting will up to 20,40,60...

  • Why? How do you know you should get to 20, 40, etc? What are your expected results? Could you post sample data in a consumable format?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I use

    select max(sequence)

    to find out max sequence number

  • adonetok (7/21/2014)


    I need help to code to add sequence number.

    One claim number may have various max sequence number.

    For example,

    1) Claim number: 111 max sequence: 17

    2) Claim number: 222 max sequence: 38

    For 1) I need code to insert 18,19,20

    For 2) I need code to insert 39,40

    That is, based on max sequence number, inserting will up to 20,40,60...

    Insert what where?

    Really need DDL, sample data, and expected results if you want any.

  • adonetok (7/21/2014)


    I use

    select max(sequence)

    to find out max sequence number

    I have to admit, I cringe a little every time I see a recommendation like that. There's just too much of a chance of either getting deadlocks if you do it right or getting duplicate values if you do it wrong.

    --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)

  • Here's an example of how to do something like that using Common Table Expressions ( CTEs ):

    USE tempdb

    GO

    IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

    CREATE TABLE #claims

    (

    claimNumberINT,

    maxSequenceINT,

    PRIMARY KEY ( claimNumber, maxSequence )

    )

    GO

    INSERT INTO #claims VALUES

    ( 111, 17 ), ( 222, 38 ), ( 333, 0 )-- won't rollup for maxSequence = 0

    GO

    ;WITH cte AS

    (

    SELECT claimNumber, MAX(maxSequence) maxSequence

    FROM #claims

    WHERE maxSequence > 0

    GROUP BY claimNumber

    ), cte2 AS

    (

    SELECT 0 x, 0 y

    UNION ALL

    SELECT x + 1, y + CASE WHEN ( x % 20 ) = 0 THEN 20 ELSE 0 END

    FROM cte2

    WHERE x < 1000-- Assumes MAX maxSequence is less than 1000

    )

    --INSERT INTO #claims ( claimNumber, maxSequence )

    SELECT c.claimNumber, z.x AS newSequenceNumber

    FROM cte c

    CROSS APPLY ( SELECT MIN(y) y FROM cte2 WHERE y > c.maxSequence ) a-- Work out the nearest multiple of 20

    CROSS JOIN cte2 z-- Roll it up to the nearest multiple of 20

    WHERE z.x > c.maxSequence

    AND a.y = z.y

    ORDER BY c.claimNumber, c.maxSequence

    OPTION ( MAXRECURSION 1000 )

    Uncomment the INSERT statement if you actually want to add the records into the #claims temp table.

    I think a numbers table would probably scale better if you have high volumes, but this example would easily convert.

    HTH

  • -- Make some sample data

    DROP TABLE #SampleTable

    SELECT *

    INTO #SampleTable

    FROM (

    SELECT TOP 17 [Claim number] = 111, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

    UNION ALL

    SELECT TOP 38 [Claim number] = 222, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

    UNION ALL

    SELECT TOP 41 [Claim number] = 333, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

    UNION ALL

    SELECT TOP 79 [Claim number] = 444, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS

    ) d

    -- Demonstrate a solution using the sample data

    SELECT

    d.[Claim number],

    [sequence number] = d.LastSequenceNumber + x.n

    FROM (

    SELECT

    [Claim number],

    RowsToConstruct = 20-(MAX([sequence number])%20),

    LastSequenceNumber = MAX([sequence number])

    FROM #SampleTable

    GROUP BY [Claim number]

    ) d

    CROSS APPLY (SELECT TOP (RowsToConstruct) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM SYSCOLUMNS) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey Bob,

    I suggest that you stop using rCTEs to count as they're a form of hidden RBAR and will cause problems. And low row counts don't justify the use of bad code.

    Check the following article: http://www.sqlservercentral.com/articles/T-SQL/74118/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (7/22/2014)


    Hey Bob,

    I suggest that you stop using rCTEs to count as they're a form of hidden RBAR and will cause problems. And low row counts don't justify the use of bad code.

    Check the following article: http://www.sqlservercentral.com/articles/T-SQL/74118/

    Didn't even catch that, but then I stopped reading his code as soon as I saw the semicolon (;) being used as a statement begininator instead of as a statement terminator. Sorry, pet peeve. Bothers the heck out of me.

  • Nice and neat, but will roll up maxSequence of 0 to 20. Not to say that the OP has any of those, maybe they do; maybe they don't : )

  • What's totally odd is that MS does that with their examples of CTEs - all of them begin with a semi-colon, which was really weird to me. Gotta love the "authority" promoting bad practices!

  • pietlinden (7/22/2014)


    What's totally odd is that MS does that with their examples of CTEs - all of them begin with a semi-colon, which was really weird to me. Gotta love the "authority" promoting bad practices!

    Who says I pay attention to their examples? :w00t:

  • Having taken all your comments into consideration, and with a similar approach to Chris, this option was best for me at scale ( 1 million ):

    USE tempdb

    GO

    IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

    CREATE TABLE #claims

    (

    claimNumberINT,

    maxSequenceINT,

    PRIMARY KEY ( claimNumber, maxSequence )

    )

    GO

    INSERT INTO #claims VALUES

    ( 111, 17 ), ( 222, 38 ), ( 333, 0 )

    GO

    IF OBJECT_ID('dbo.numbers') IS NULL

    BEGIN

    CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )

    ;INSERT INTO dbo.numbers ( x )

    SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x

    FROM master.sys.columns c1

    CROSS JOIN master.sys.columns c2

    CROSS JOIN master.sys.columns c3

    ORDER BY 1

    END

    -- This was good at scale

    SELECT claimNumber, maxSequence + x.x AS maxSequence

    FROM (

    SELECT

    claimNumber,

    MAX(maxSequence) maxSequence,

    ( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

    FROM #claims

    WHERE maxSequence > 0

    GROUP BY claimNumber

    ) c

    CROSS JOIN dbo.numbers x

    WHERE x.x <= [rollupTo] - maxSequence

    I prefer numbers table to syscolumns, eg you have to be careful over on sqlfiddle where that view only has 48 rows.

  • @wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.


    [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]

  • wBob (7/22/2014)


    Having taken all your comments into consideration, and with a similar approach to Chris, this option was best for me at scale ( 1 million ):

    USE tempdb

    GO

    IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

    CREATE TABLE #claims

    (

    claimNumberINT,

    maxSequenceINT,

    PRIMARY KEY ( claimNumber, maxSequence )

    )

    GO

    INSERT INTO #claims VALUES

    ( 111, 17 ), ( 222, 38 ), ( 333, 0 )

    GO

    IF OBJECT_ID('dbo.numbers') IS NULL

    BEGIN

    CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )

    ;INSERT INTO dbo.numbers ( x )

    SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x

    FROM master.sys.columns c1

    CROSS JOIN master.sys.columns c2

    CROSS JOIN master.sys.columns c3

    ORDER BY 1

    END

    -- This was good at scale

    SELECT claimNumber, maxSequence + x.x AS maxSequence

    FROM (

    SELECT

    claimNumber,

    MAX(maxSequence) maxSequence,

    ( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

    FROM #claims

    WHERE maxSequence > 0

    GROUP BY claimNumber

    ) c

    CROSS JOIN dbo.numbers x

    WHERE x.x <= [rollupTo] - maxSequence

    I prefer numbers table to syscolumns, eg you have to be careful over on sqlfiddle where that view only has 48 rows.

    Pet peeve time again. Now there is a semicolon in front the INSERT statement.

    Issue 1, ORDER BY 1. IIRC ORDER BY ordinal has been deprecated and may be removed from future versions of SQL Server. Also, it obfuscates what you are sorting on and, if the column list changes can easily break. You really should explicitly specify the column name(s) you are sorting on.

    Using a CTE your new code would look like this:

    WITH c as (

    SELECT

    claimNumber,

    MAX(maxSequence) maxSequence,

    ( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

    FROM #claims

    WHERE maxSequence > 0

    GROUP BY claimNumber

    )

    SELECT

    claimNumber,

    maxSequence + x.x AS maxSequence

    FROM

    c

    CROSS JOIN dbo.numbers x

    WHERE

    x.x <= [rollupTo] - maxSequence;

    There is more but I really should do some work right now.

Viewing 15 posts - 1 through 15 (of 20 total)

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