Post reply

incrementing

  • April 23, 2013 at 8:46 pm

    #274516

    I have this table with these info:

    IDint

    Firstorderint

    Secondorderint

    Thirdorderint

    sequenceNumberint

    How would I write a script so that the result would be:

    Before the script:

    IDfirstordersecondorderthirdordersequencenumber

    1nullnullnull1

    2nullnullnull2

    100000 nullnullnull100000

    The logic for the script is:

    The initial value for firstorder, secondorder, and thirdorder is 1

    for 1..10 (sequencenumber) : first order would be 1, from 11..20 would be 2, etc…

    for 1..100 (sequencenumber): secondorder would be 1, from 101 to 200 would be 2, etc…

    for 1..10000 (sequencenumber): thirdorder would be 1, from 10001 to 20000 would be 2, etc..

    After executing the script:

    IDfirstordersecondorderthirdordersequencenumber

    11111

    1011110

    1121111

    Thanks for all the help.

  • April 23, 2013 at 9:26 pm

    #1609327

    -- Set up test data

    declare @test-2 table (

    SequenceNumber int

    );

    insert into @test-2 values (1);

    insert into @test-2 values (2);

    insert into @test-2 values (10);

    insert into @test-2 values (11);

    insert into @test-2 values (100);

    insert into @test-2 values (101);

    insert into @test-2 values (10000);

    insert into @test-2 values (10001);

    -- Query

    selectSequenceNumber,

    ((SequenceNumber - 1) / 10) + 1 as FirstOrder,

    ((SequenceNumber - 1) / 100) + 1 as SecondOrder,

    ((SequenceNumber - 1) / 10000) + 1 as ThirdOrder

    from @test-2

  • April 23, 2013 at 9:58 pm

    #1609331

    Thank you very much for your solution. However, I probably didn't explain clearly enough.

    The first sequence number doesn't necessarily start out with 1, it could start with 567891011 or what ever number

    but then it sequentially incremented so the next sequence number would be 567891012, etc...

    There is a first sequence number and last sequence number, for example: 567891011 to 567991011

    The logic for calculating firstorder, secondorder, and thirdorder is also based on the quantity (last sequence - first sequence)

  • April 23, 2013 at 10:01 pm

    #1609332

    You can write a subquery that will return the min sequence number, and use that as an offset to the real sequence number value 🙂

    Edit: This highlights why it is imperative that questions come with robust sample data and expected results 😀

  • April 24, 2013 at 8:57 am

    #1609560

    The best approach would be to normalize your base table. 😀

    To get the results you are looking for you need to use a cross tab. You can find two great article about that in my signature.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 5 posts - 1 through 4 (of 4 total)

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