Is there a way to assign a number to a record (of in a range) and when it ends it starts from the first number of the range?
I currently have the following table:
| id | date | first_number | last_number | start_number |
|---|---|---|---|---|
| 1 | 01/01/2021 | 1 | 3 | 2 |
| 1 | 02/01/2021 | 1 | 3 | 2 |
| 1 | 03/01/2021 | 1 | 3 | 2 |
| 1 | 04/01/2021 | 1 | 3 | 2 |
| 1 | 05/01/2021 | 1 | 3 | 2 |
| 1 | 06/01/2021 | 1 | 3 | 2 |
| 1 | 07/01/2021 | 1 | 3 | 2 |
| 1 | 08/01/2021 | 1 | 3 | 2 |
I would like to get the value assignment in the cycle column based on start_range and last_range. The start of the cycle must be the value of the first record (in example 2)
| id | date | first_number | last_number | start_number | cycle |
|---|---|---|---|---|---|
| 1 | 01/01/2021 | 1 | 4 | 2 | 2 |
| 1 | 02/01/2021 | 1 | 4 | 2 | 3 |
| 1 | 03/01/2021 | 1 | 4 | 2 | 4 |
| 1 | 04/01/2021 | 1 | 4 | 2 | 1 |
| 1 | 05/01/2021 | 1 | 4 | 2 | 2 |
| 1 | 06/01/2021 | 1 | 4 | 2 | 3 |
| 1 | 07/01/2021 | 1 | 4 | 2 | 4 |
| 1 | 08/01/2021 | 1 | 4 | 2 | 1 |
CodePudding user response：
You can use ROW_NUMBER to achieve this, with a modulo operator and some addition:
SELECT id,
date,
first_number,
last_number,
start_number,
ISNULL(NULLIF((ROW_NUMBER() OVER (ORDER BY [date]) start_number - first_number) % last_number,0),last_number)
FROM (VALUES(1,CONVERT(date,'01/01/2021',103),1,3,2),
(1,CONVERT(date,'02/01/2021',103),1,3,2),
(1,CONVERT(date,'03/01/2021',103),1,3,2),
(1,CONVERT(date,'04/01/2021',103),1,3,2),
(1,CONVERT(date,'05/01/2021',103),1,3,2),
(1,CONVERT(date,'06/01/2021',103),1,3,2),
(1,CONVERT(date,'07/01/2021',103),1,3,2),
(1,CONVERT(date,'08/01/2021',103),1,3,2))V(id,date,first_number,last_number,start_number);
The ISNULL/NULLIF are there to replace 0 with the last value.
You may/will need to add a PARTITION BY clause depending on your requirements for other id values.
Note that the results here don't match your expected results, as in your expected results last_number has a value of 4, but has the value 3 in your sample data.