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?
CodePudding user response:
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