Categorized Amounts per year

Transact-SQL
1

Hi Everyone,

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!!!

Your SQLBunny

2

Sorry that the table I posted looks like that. It looked okay in the preview :slight_smile:

3

I don't see a need to sum up rows, so this looks pretty straightforward, guess I'm missing something?

SELECT ...
    CASE WHEN [Amt. 2015] >= 7000 THEN 'Large'
         WHEN [Amt. 2015] >= 5000 THEN 'Medium'
         ELSE 'Small' END AS Category
    
FROM table_name tn
WHERE [Amt. 2015] >= 4000
4

Can you post a script to create the table and some data sample. That way we can know the data type of each column and if it's possible try to explain the purpose of each column. It would be easier to help you that way.