I have a little problem. I’m sure I’ve done it before, but for the life of me….I don’t remember how.
It would be great if you could help me out. Please keep in mind, that I have "read only rights" on the tables and can only do this with simple SELECT queries.
So I have a couple of tables. But the one I have problems with is the table with the payment information. The goal is a list of all people who paid (without canceled amounts)>= 4000€ in 2015 (in total – no single payments) including the quantity of those payments for the year. But I also need the:
- Total quantity and amount (lifetime payment)
- Total quantity and amount for the last 3 years individually(some have 0€ in 2013, 200€ in 2014 but must
have >= 4000€ in 2015)
I tried it with CASE statements. That worked really well. (Subselects in the SELECT section did not quite work that well). The problem I have is I don’t know where to specify that I only want the ones in the list that have >=4000€ in 2015.
ID|Total Qty.|Total amt.|Qty. 2013|Amt. 2013|Qty. 2014|Amt. 2014|Qty. 2015|Amt. 2015|Category
1 30 30000 0 0 3 5000 6 6000 Medium
2 5 8000 0 0 0 0 2 7000 Large
3 7 4000 0 0 0 0 7 4000 Small
While we’re at it. Could you also help out with the column Category. Here I have to categorize the donations for the year 2015 in for example categories small, medium, large. Like when person has an amount of 4000-5000 he is considered small, 5000-7000 is medium and 7000-…. Is large. Do you haven an idea for that?
Thank you already in advance!!!