I am trying to update rows in a sql table based on a ratio. The ratio is set in a different table (1:1, 2:1, 3:1, 1:2, 1:3, 2:3 for example)
The table consists of a key number and a status (0 = Run, 3 = Don't Run)
When the table is first populated, all of the Status columns are = 0
Each item in the table will run on either side of the ratio, but never both sides.
3:1 ratio is shown, but any iteration between 1:1 and 9:9 is possible. Required Data Out1 is always based on the left digit, Required Data Out is always based on the right digit. The table itself is populated daily and the row count changes each day, usually between 800 and 1200 keys.
Google and Stack Exchange show 1,000 different ways to calculate ratios in SQL, but nothing on how to implement them that I could find. Any ideas?
I have tried various permutations of WHILE loops, with mixed results. As long as the total row count can be evenly divided by both numbers in the ratio, as well as the sum of the 2 numbers, I get the results I need. But in most other cases, the Required Data Out1 group is good, but the Required Data Out2 group is skewed by the remainder of the division.
As mention in the comments earlier, you can use modulus operator to evaluate the required condition for your DataOut 1 or 2.
The query below is using generate_series() to simulate the Key and Status of your table is show as column Status1 (Data Out1) and Status2 (Data Out2).
If your Key is not continuous, you will need to use row_number() to generate a continuous running number
-- ratio x:y 3:1
declare @x int = 3, @y int = 1;
declare @z int = @x + @y;
select [Key] = value,
Status1 = case when n.n < @x then 0 else 3 end,
Status2 = case when n.n < @x then 3 else 0 end
from generate_series(1,10,1) s
cross apply
(
select n = (s.value - 1) % @z
) n
Result :
| Key | Status1 | Status2 |
|---|---|---|
| 1 | 0 | 3 |
| 2 | 0 | 3 |
| 3 | 0 | 3 |
| 4 | 3 | 0 |
| 5 | 0 | 3 |
| 6 | 0 | 3 |
| 7 | 0 | 3 |
| 8 | 3 | 0 |
| 9 | 0 | 3 |
| 10 | 0 | 3 |