Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

incrementing Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 8:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:14 AM
Points: 43, Visits: 179
I have this table with these info:
ID int
Firstorder int
Secondorder int
Thirdorder int
sequenceNumber int

How would I write a script so that the result would be:
Before the script:
ID firstorder secondorder thirdorder sequencenumber
1 null null null 1
2 null null null 2
100000 null null null 100000

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:
ID firstorder secondorder thirdorder sequencenumber
1 1 1 1 1
10 1 1 1 10
11 2 1 1 11

Thanks for all the help.
Post #1445750
Posted Tuesday, April 23, 2013 9:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
-- Set up test data
declare @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);

-- Query
select SequenceNumber,
((SequenceNumber - 1) / 10) + 1 as FirstOrder,
((SequenceNumber - 1) / 100) + 1 as SecondOrder,
((SequenceNumber - 1) / 10000) + 1 as ThirdOrder
from @test

Post #1445753
Posted Tuesday, April 23, 2013 9:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:14 AM
Points: 43, Visits: 179
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)
Post #1445757
Posted Tuesday, April 23, 2013 10:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse