how to generate next #

  • 1 3202306000

    2 3202307500

    3 3202307503

    how can i generate example 1's next upto four # such as 3202306001 to 3202306004 next example 2 and 3 since fourth # is example 3 already so need only inbetween #'s

  • I don't know if you figured it out already or you still need help, but I know I don't quite understand the question.

  • Very little code to work with here, but have you looked at the ROW_NUMBER function in Books Online? I think that might help you do what you want.

    If not, post a bit more code & structure to help us understand the problem better.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 1 3202306000 (already in db)

    3202306001 (generated #)

    3202306002 (generated #)

    3202306003 (generated #)

    3202306003 (generated #, since requirment upto 4, it stop here and see next # )

    2 3202307500 (already in db)

    3202307501 (generated #)

    3202307502 (generated #)

    3202307503 (generated #)

    3202307504 (generated #, since requirment upto 4, it stop here and see next # )

    3 3202307503 (already in db)

    3202307504 (generated #)

    3202307505 (generated #)

    3202307506 (generated #)

    3202307507 (generated #, since requirment upto 4, it stop here and see next # )

    how can i generate example 1's next upto four # such as 3202306001 to 3202306004 next example 2 and 3 since fourth # is example 3 already so need only inbetween #'s

  • ok, I'm making a lot of assumptions, like ther emust be 3 more items added for EVERY item that's in your sample table. if an item overlaps where id_3, you are out of luck

    this works;

    CREATE TABLE TMP(TMPID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,NEALSID BIGINT)

    INSERT INTO TMP(NEALSID)

    SELECT 3202306000 UNION

    SELECT 3202307500 UNION

    SELECT 3202307503

    SELECT * INTO #SAVED FROM (

    SELECT NEALSID FROM TMP UNION

    SELECT NEALSID + 1 FROM TMP UNION

    SELECT NEALSID + 2 FROM TMP UNION

    SELECT NEALSID + 3 FROM TMP)X

    INSERT INTO TMP(NEALSID )

    SELECT #SAVED.NEALSID FROM #SAVED

    LEFT OUTER JOIN TMP ON #SAVED.NEALSID=TMP.NEALSID

    WHERE TMP.TMPID IS NULL

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • DECLARE@Sample TABLE

    (

    ID BIGINT

    )

    INSERT@Sample

    SELECT3202306000 UNION ALL

    SELECT3202307500 UNION ALL

    SELECT3202307503

    SELECT DISTINCTs.ID,

    s.ID + x.Number AS Yak

    FROM@Sample AS s

    CROSS JOIN(

    SELECT0 AS Number UNION ALL

    SELECT1 UNION ALL

    SELECT2 UNION ALL

    SELECT3 UNION ALL

    SELECT4

    ) AS x


    N 56°04'39.16"
    E 12°55'05.25"

  • With only a little modification, you could use the code in this article:

    http://www.sqlservercentral.com/articles/T-SQL/61461/[/url]

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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