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
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;
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
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......
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)?
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!!!!!!!!!!!!!!!
15 People are following this question.
