CODEWITHSUNDEEP
mysql
Lazykiddy
Lazykiddy

Reputation: 1535

Detecting negative change and acting upon this

I have this table where I only want to look at AB

ID     CODE       COUNT   
102    AB         9
101    AB         8
100    AC         23    //not important!!!!
99     AB         7
98     AB         6
97     AB         5
96     AB         0

And I then want to count the differences between the specific ID's which have CODE 'AB'

So

Step 1: 9 - 8 = 1   // (corrected sample data. this line was wrong)
Step 2: 8 - 7 = 1
Step 3: 7 - 6 = 1
Step 4: 6 - 5 = 1
Step 5: 5 - 0 = 5

This I do by this query made by @bonCodigo

select ID, DIFFERENCE, 
COUNT from (
    SELECT
    t.ID, t.CODE, t.COUNT,
    @PREVCOUNT,
    @PREVCOUNT - t.COUNT DIFFERENCE,
    @PREVCOUNT := t.COUNT  -- Updates for the next iteration, so it
                           -- must come last!
    FROM
    (SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
    (SELECT @PREVCOUNT := NULL) _uv
    group by t.id, t.code
    )x
where x.difference >= 0
order by ID DESC;

As my new incoming data sometimes RESETS the count, it starts counting from 0 till whatever.

So sometimes I get data in this order:

ID        COUNT
1.        0
2.        1
3.        2
4.        7
5.        4     // which means the counter has reset to 0 and counted up to 4 again.
6.        5

Now what my query is doing, it counts only the positive changes and takes this as a difference.

So what it does:

Step 1: 1 - 0 = 1
Step 2: 2 - 1 = 1
Step 3: 7 - 2 = 5
Step 4: 4 - 7 = -3 //discarded as this difference is smaller than 0
Step 5: 5 - 4 = 1

So if I SUM() this I get 8 http://sqlfiddle.com/#!2/6924a/2

While I want this code to count from 0 as soon as there is a negative difference

So what I want:

Step 1: 1 - 0 = 1
Step 2: 2 - 1 = 1
Step 3: 7 - 2 = 5
Step 4: 4 - 7 = -3  BUT MAKE IT 4 because the counter started from 0 again.
Step 5: 5 - 4 = 1

So if I SUM() this I get 12

Upvotes: 0

Views: 53

Answers (1)

Jirilmon
Jirilmon

Reputation: 1944

An IF statement will do the trick.

IF((@PREVCOUNT - t.COUNT) < 0, @PREVCOUNT, (@PREVCOUNT - t.COUNT)) DIFFERENCE

Here is the Working fiddle: http://sqlfiddle.com/#!2/6924a/7/0

And the modified query is:

select ID, DIFFERENCE, 
COUNT from (
    SELECT
    t.ID, t.CODE, t.COUNT,
    @PREVCOUNT,
    IF((@PREVCOUNT - t.COUNT) < 0, @PREVCOUNT, (@PREVCOUNT - t.COUNT)) DIFFERENCE,
    @PREVCOUNT := t.COUNT  -- Updates for the next iteration, so it
                           -- must come last!
    FROM
    (SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
    (SELECT @PREVCOUNT := NULL) _uv
    group by t.id, t.code
    )x
where x.difference >= 0
order by ID DESC;

Upvotes: 1

Related Questions