question

Newbee avatar image
Newbee asked

Assining numbers based on the start Position

This is my first post, in a hope of getting some help. I have a scenario where in I need to assign rotation type for the ID's. Following example would help in understanding better: Need to calculate the 3rd column. it need not be always 1,2,3,4. It can start with 4,3,2,1 or 3,2,4,1 or 2,3,4,5 based on some calculated start point. Need to accomplish this in Sql server 2008 with set based approach Id Class Rotation Type to assigned 7001 1 1 7001 2 2 7001 3 3 7001 4 4 7001 5 1 7001 6 2 7001 7 3 7001 8 4 7001 9 1 7001 10 2 7001 11 3 7001 12 4 7001 13 1 7001 14 2 7002 15 3 7002 1 1 7002 2 2 7002 3 3 7002 4 4 7002 5 1 7002 6 2 7002 7 3 7002 8 4 7002 9 1 7002 10 2 7002 11 3 7002 12 4 7002 13 1 so on.......... Please help me as I am new to SQL server.Thanks in Advance
sqlserver2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
mjharper avatar image
mjharper answered
I'm not sure i've understood exactly what you're trying to do - but if you need a new Id that cycles every 4 rows you could use the modulo function (%). For example below I've created a table with the first few rows of your data in and then applied the it to the class column. Let me know if I've misunderstood... IF OBJECT_ID('tempdb..#testData') IS NOT NULL DROP TABLE #testData CREATE TABLE #testData ( Id INT , Class INT ) INSERT INTO #testData ( Id, Class) VALUES ( 7001, 1 ), ( 7001, 2 ), ( 7001, 3 ), ( 7001, 4 ), ( 7001, 5 ), ( 7001, 6 ), ( 7001, 7 ), ( 7001, 8 ), ( 7001, 9 ), ( 7001, 10 ), ( 7001, 11 ), ( 7001, 12 ), ( 7001, 13 ), ( 7001, 14 ), ( 7001, 15 ), ( 7002, 1 ), ( 7002, 2 ), ( 7002, 3 ), ( 7002, 4 ), ( 7002, 5 ) DECLARE @startPoint INT = 1; SELECT td.Id , td.Class , CASE WHEN ( td.Class + @startPoint - 1 ) % 4 = 0 THEN 4 ELSE ( td.Class + @startPoint - 1 ) % 4 END AS Rotation FROM #testData td;
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you so much mjharper .you understood it right. This holds good for rotation type 1,2,3,4. But the rotation type need not always start with 1,2 ,3 4 depends on another column startpoint : if start point = 1 then it is 1,2,3,4,1,2,3,4 etc --yourquery would work here if start point = 2 then it is 2,3,4,1,2,3,4,1 etc if start point = 3 then it is 3,4,1,2,3,4,1,2 etc if the start point = 4 then 4,3,2,1,4,3,2,1,4 etc
0 Likes 0 ·
Hi - i've updated my original answer. I've declared a @startPoint variable - but that could also be a column if necessary. I've also added a CASE statement so it shows 4 not zero. Hopefully that does what you need. Any questions let me know. Cheers
0 Likes 0 ·
Thank you so much for this ..perfect...But is there a possiblity to derive at some logic for the following series.. 5,6,7,8,9. Based on the start point rotation should change ... Start point 5 then 5,6,7,8,9,5,6,7,8,9,5,6 so on Start point 6 then 6,7,8,9,5,6,7,8,9,5,6,7 so on Start point 7 then 7,8,9,5,6,7,8,9,5,6....... Start point 8 then 8,9,5,6,7 ,8,9,5,6,7.... Start point 9 then 9,5,6,7,8,9,5,6,7,8......
0 Likes 0 ·
Hi - for the first set of starting points (1-4) the rotation cycled through 4 numbers (1-4). In the next set (5-9) it appears to be cycling around 5 numbers (5-9). Is that correct or should it still cycle through 4 numbers (5-8)? If it's 5 numbers what happens when starting point = 10? does that cycle around 6 numbers (10-15)?
0 Likes 0 ·
Yup now it is cycle 5... But it starts from 5 and not 1.... Based on the logic you gave I simply added 4 to the resultant to achieve 5,6,7,8,9 and the other combination. start point is 1 for 5, 2 for 6 so on....and then add 4 Thanks again for providing such a simple logic...u r simply great!!!!!!!!!!!!!!!
0 Likes 0 ·
Correct me if I'm wrong, but I don't think the current logic will work for start point of 5 though will it? If you add 4 to the current results you will get 5,6,7,8,5 not 5,6,7,8,9
0 Likes 0 ·
Yes, this time it would be % 5...and not 4....
0 Likes 0 ·
ok - do you need to do that automatically - or will you just alter the script? How high can the start point go? does something else happen if start point = 10?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.