SQL query count (recursive)

Question

I have the following table on my database which contains some transactions for which I need to calc points and rewards. Every time a TxType A occurs I should record 10 points. Then I have to subtract from these points the value of the PP column every time a TxType B occurs. When the calculation goes to zero a reward is reached.

ID  TxType  PP
1     A    0
2     B    2
3     B    1
4     B    1
5     B    1
6     B    3
7     B    1
8     B    1
9     A    0
10    B    4
11    B    3
12    B    2
13    B    1
14    A    0
15    B    2

I have created the sql query to calc points as follow

SELECT SUM(
   CASE 
      WHEN TxType = 'A' THEN 10
      WHEN TxType = 'B' THEN (PP * -1) 
   END)
FROM myTable

This query return the value of 8, which is exactly the number of points based on the sample data. How do I calculate the rewards occurred (2 in the given example)?

thanks for helping

Can you clarify "This query return the value of 8, which is exactly the number of points based on the sample data. How do I calculate the rewards occurred (2 in the given example)?" The query returns an entire data set. So its hard to tell what you're saying. — Khan
– Khan, Commented Apr 15, 2014 at 13:44
@Khan: sorry but my query just return a value, not an entire dataset. Did you missed the SUM function? The result 8 comes from 30 (value for each type A tx) less 22 (sum of PP when tx type B) — Lorenzo
– Lorenzo, Commented Apr 15, 2014 at 13:47
@Khan: 2 are the rewards achieved. The customer get 1 reward everytime his card reach 0 points. TX id from 1 to 8 is the first reward, and from 9 to 13 the second one. — Lorenzo
– Lorenzo, Commented Apr 15, 2014 at 14:07

Gordon Linoff · Accepted Answer · 2014-04-15 13:40:57Z

3

One way to do the calculation (in SQL Server 2008) using a correlated subquery:

select t.*,
       (select sum(case when TxType = 'A' then 10
                        when TxType = 'B' then PP * -1
                   end)
        from mytable t2
        where t2.id <= t.id
       ) as TheSum
from mytable t;

You can then apply the logic of what happens when the value is 0. In SQL Server 2012, you could just use a cumulative sum.

edited Apr 15, 2014 at 13:40

answered Apr 15, 2014 at 13:34

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Khan Over a year ago

I think you're missing a ) after where t2.id <= t.id

Khan Over a year ago

Yw.. +1, from what I can tell. This is what I'm expecting the OP is looking for.

Community · Accepted Answer · 2017-05-23 10:32:26Z

1

To complete Gordon Linoff's the answer, you just need to count the records where TheSum is 0 to get how many rewards occurred:

SELECT COUNT(1)
FROM (
    SELECT  ID, 
            TxType, 
            PP,
            (   SELECT  SUM(CASE TxType WHEN 'A' THEN 10 WHEN 'B' THEN -PP END)
                FROM    #myTable t2
                WHERE   t2.id <= t1.id
            ) AS TheSum
    FROM #myTable t1
) Result
WHERE TheSum = 0

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Apr 15, 2014 at 14:30

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Collectives™ on Stack Overflow

SQL query count (recursive)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related