## incrementing

 Author Message phamm SSC-Enthusiastic Group: General Forum Members Points: 125 Visits: 190 I have this table with these info:ID intFirstorder intSecondorder intThirdorder intsequenceNumber intHow would I write a script so that the result would be:Before the script:ID firstorder secondorder thirdorder sequencenumber 1 null null null 12 null null null 2100000 null null null 100000The logic for the script is: The initial value for firstorder, secondorder, and thirdorder is 1for 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:ID firstorder secondorder thirdorder sequencenumber1 1 1 1 110 1 1 1 1011 2 1 1 11Thanks for all the help. Mansfield Right there with Babe Group: General Forum Members Points: 720 Visits: 394 `-- Set up test datadeclare @test table ( SequenceNumber int);insert into @test values (1);insert into @test values (2);insert into @test values (10);insert into @test values (11);insert into @test values (100);insert into @test values (101);insert into @test values (10000);insert into @test values (10001);-- Queryselect SequenceNumber, ((SequenceNumber - 1) / 10) + 1 as FirstOrder, ((SequenceNumber - 1) / 100) + 1 as SecondOrder, ((SequenceNumber - 1) / 10000) + 1 as ThirdOrderfrom @test` phamm SSC-Enthusiastic Group: General Forum Members Points: 125 Visits: 190 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 567991011The logic for calculating firstorder, secondorder, and thirdorder is also based on the quantity (last sequence - first sequence) Mansfield Right there with Babe Group: General Forum Members Points: 720 Visits: 394 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 :-D Sean Lange SSC Guru Group: General Forum Members Points: 65102 Visits: 17979 The best approach would be to normalize your base table. :-DTo 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.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)