SQL compare the incremental difference from a column

Let’s say I have a table with the columns CurrentDateTime and CurrentValue where the CurrentValue column mostly increments. Sometimes though, the CurrentValue resets, this is where the issues start.

So, if it weren’t for the reset every now and then, I could just do this:

SELECT MAX(CurrentValue)-MIN(CurrentValue) AS AccumulateValue 
FROM Table
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0);

If the CurrentValue resets, the AccumulateValue is wrong.

Let’s say these are the values I got:

CurrentDateTime    CurrentValue
-------------------------------
2019-10-01            100
2019-10-02            101
2019-10-03            102
2019-10-04            103
2019-10-05            104
2019-10-06            105

This would return 5

But if the values looks like this:

CurrentDateTime    CurrentValue
-------------------------------
2019-10-01            100
2019-10-02            101
2019-10-03            102
2019-10-04            103
2019-10-05            104
2019-10-06              0
2019-10-07              1
2019-10-08              2
2019-10-09              3

The result would be 101, and it should be 7.

I did try this (calculating the difference between each row and run SUM only on positive values):

SELECT SUM(CurrentValue)
FROM
    (SELECT 
         tb1.CurrentDateTime,
         CASE 
            WHEN tb1.CurrentValue > tb1.CurrentValueLag 
               THEN (tb1.CurrentValue - tb1.CurrentValueLag)
            WHEN tb1.CurrentValue <= tb1.CurrentValueLag
               THEN 0
         END AS CurrentValue
     FROM
         (SELECT 
              *,
              LAG(CurrentValue) OVER (ORDER BY CurrentDateTime ASC) AS CurrentValueLag 
          FROM Table) AS tb1) AS tb2
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0);

This table is pretty large with small increments, and the SUM isn’t correct for some reason I can’t figure out. I noticed when comparing a month (with no reset), the value from this last query shows a higher value than the correct value.

Answer

First of all. Thanks for all the help I got.

Apparently, the table also had some corrupted data that made this somewhat messy. On top of that, the data could also decrease, wich I didn’t take into account.

Anyway, I needed to do some Changes to my code, but here it is:

SELECT YEAR(CurrentDateTime)
,MONTH(CurrentDateTime)
,SUM(tb2.CurrentValue) AS tb3.CurrentValue
 FROM(
     SELECT tb2.CurrentDateTime
    ,(tb2.CurrentValue - tb2.CurrentValue_LAG) AS CurrentValue --Only do math where there's not NULL.
     FROM(
         SELECT *
         , LAG(tb1.CurrentValue) OVER (ORDER BY tb1.CurrentDateTime ASC) AS 
         CurrentValue_LAG
         FROM(
             SELECT CurrentDateTime
             ,CASE WHEN CurrentValue = 0 THEN NULL ELSE CurrentValue END AS CurrentValue --NULLING the values to exclude them from the tb3 substract operation

             FROM TableOne
             WHERE CurrentDateTime BETWEEN '2019-01-01' AND '2019-12-31'
         ) AS tb1
     ) AS tb2
 ) AS tb3
GROUP BY YEAR(CurrentDateTime), MONTH(CurrentDateTime)

It might look messy, but it returns the correct values.