The Difference Between Each Row Value - Sum Error

My main query was solved at the following post TSQL - Get the difference between each row value I have one problem of summing the reading values between each row. id device_id t

Solution 1:

What you essentially need is to pretend temporarily that c2.reading didn't wrap around after reaching 1,000,000, and that only when c2.reading < c1.reading. That is, at that point you'd need to increase c2.reading by 1,000,000, then subtract c1.reading. And when c2.reading >= c1.reading, the query should calculate the "normal" difference, i.e. subtract c1.reading from the original (non-increased) c2.reading value.

One way to achieve that logic would be to do something as straightforward as this:

SUM(
  CASEWHEN c2.reading < c1.reading THEN1000000ELSE0END+ c2.reading
  - ISNULL(c1.reading, c2.reading)
) AS Count1

However, there's also a different approach.

Your reading values, and, as a consequence, differences between any two of them as well, can never exceed 1,000,000. Therefore, you can freely apply modulo 1,000,000 to a positive difference and that'll give you the same difference back:

d mod1,000,000 = d

Moreover, adding multiples of 1,000,000 to a positive difference won't affect the result of modulo 1,000,000 because, according to the distributiveness of the modulo operation,

  (d + 1,000,000 * n) mod1,000,000 =
= d mod1,000,000 + (1,000,000 * n) mod1,000,000

The first summand, d mod 1,000,000 results in d, the second one, (1,000,000 * n) mod 1,000,000 yields 0, d + 0 = d.

On the other hand, adding 1,000,000 to a negative difference would give us a correct positive difference.

So, to sum up,

• adding 1,000,000 to a negative difference gives us a (correct) positive difference,

• a positive difference modulo 1,000,000 yields the same positive difference, and

• adding 1,000,000 to a positive difference doesn't affect the result of modulo 1,000,000.

Taking all that into account, we can end up with the following universal expression to calculate a single difference:

(1000000 + c2.reading - ISNULL(c1.reading, c2.reading)) % 1000000

where % is the modulo operator in Transact-SQL.

Put the expression into SUM to get the corresponding aggregated values:

SUM((c2.reading + 1000000 - ISNULL(c1.reading, c2.reading)) % 1000000) AS Count1