Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 incrementing Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 23, 2013 8:46 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, February 11, 2015 11:20 AM Points: 43, 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.
Post #1445750
 Posted Tuesday, April 23, 2013 9:26 PM
 Old Hand Group: General Forum Members Last Login: Sunday, April 10, 2016 3:13 PM Points: 364, 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`
Post #1445753
 Posted Tuesday, April 23, 2013 9:58 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, February 11, 2015 11:20 AM Points: 43, 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)
Post #1445757
 Posted Tuesday, April 23, 2013 10:01 PM
 Old Hand Group: General Forum Members Last Login: Sunday, April 10, 2016 3:13 PM Points: 364, 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
Post #1445758
 Posted Wednesday, April 24, 2013 8:57 AM
 SSCoach Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:08 PM Points: 16,145, Visits: 16,850
 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 Moden's 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)
Post #1446042

 Permissions