SQL Count depending on certain conditions

I have two tables.

One have userid and email (users table). The other have payments information (payments table) from the userid in users.

users
+--------+------------+
| Userid |    Name    |
+--------+------------+
|      1 | Alex T     |
|      2 | Jeremy T   |
|      3 | Frederic A |
+--------+------------+

payments
+--------+-----------+------------+----------+
| Userid | ValuePaid | PaidMonths | Refunded |
+--------+-----------+------------+----------+
|      1 |         1 |         12 | null     |
|      1 |        20 |         12 | null     |
|      1 |        20 |         12 | null     |
|      1 |        20 |          1 | null     |
|      2 |         1 |          1 | null     |
|      2 |        20 |         12 | 1        |
|      2 |        20 |         12 | null     |
|      2 |        20 |          1 | null     |
|      3 |         1 |         12 | null     |
|      3 |        20 |          1 | 1        |
|      3 |        20 |          1 | null     |
+--------+-----------+------------+----------+

I want to count the PaidMonths taking in consideration the following rules:

If ValuePaid < 10 PaidMonths should be = 0.23 (even if in the column the value seen is any other mumber).
If Refund=1 the PaidMonths should be = 0.

Based on this when i join both tables by userid, and sum the PaidMonths based in the previousrules, i expect to see as result:

+--------+------------+------------+
| userid |    Name    | paidMonths |
+--------+------------+------------+
|      1 | Alex T     | 25.23      |
|      2 | Jeremy T   | 13.23      |
|      3 | Frederic A | 1.23       |
+--------+------------+------------+

Can you help me to achieve this in the most elegant way? Should a temporary table be used?

Answer

The following gives your desired results, using apply with case expression to map your values:

select u.UserID, u.Name, Sum(pm) PaidMonths
from users u join payments p on p.userid=u.userid
cross apply (values(
    case 
      when valuepaid <10 then 0.23
      when Refunded=1 then 0
    else PaidMonths end
))x(pm)
group by u.UserID, u.Name

See Working Fiddle

Advertisement

Answer