CODEWITHSUNDEEP
sqlsql-servert-sql
Bas Pauw
Bas Pauw

Reputation: 262

Calculate column and include IF statement based on calculated column

I have an issue with a query that I'm trying to write. The query is supposed to give me the client number, the total revenue from that client and based on the revenue another value (i.e. 'Gold', 'Silver' or 'Bronze'). I have a query that gives me the total revenue for each client:

CREATE PROCEDURE sp_klantOverzicht 
DECLARE @beginDatum DATETIME
DECLARE @eindDatum DATETIME
--AS
SET @beginDatum = '2013-01-01 00:00:00.000';
SET @eindDatum = '2017-01-01 00:00:00.000';
SELECT KLANT_NR, SUM(PRIJS) AS OMZET
FROM (
    SELECT kl.KLANT_NR, SUM(DATEDIFF(d, h.STARTDATUM, h.EINDDATUM)*a.PRIJS_PER_D) AS PRIJS
    FROM HUUROVEREENKOMST AS h
    LEFT JOIN ARTIKEL AS a
    ON h.BARCODE = a.BARCODE
    LEFT JOIN KLANT AS kl 
    ON kl.KLANT_NR = h.KLANT_NR
    WHERE DATEPART(year, h.STARTDATUM) BETWEEN DATEPART(year, @beginDatum) 
    AND DATEPART(year, @eindDatum)
    GROUP BY kl.KLANT_NR
    UNION
    SELECT kl.KLANT_NR, SUM(a.PRIJS) AS PRIJS
    FROM ARTIKEL AS a
    LEFT JOIN KOOPOVEREENKOMST AS k
    ON k.BARCODE = a.BARCODE
    LEFT JOIN KLANT AS kl 
    ON kl.KLANT_NR = k.KLANT_NR
    WHERE DATEPART(year, k.DATUM) BETWEEN DATEPART(year, @beginDatum) 
    AND DATEPART(year, @eindDatum)
    GROUP BY kl.KLANT_NR
) AS overzicht
GROUP BY KLANT_NR
ORDER BY OMZET DESC

It will give me a result like this:

enter image description here

Now, what I need is something that checks if the value in the 'OMZET' column is above either 50, 100 or 150 and then adds 'Bronze', 'Silver' or 'Gold' in a third column.

I've tried to achieve this with a sub query within the SELECT statement, aswell as creating a function to calculate the third column. But with no result. Could anyone help me get to the correct solution?

All help would be appreciated!

Upvotes: 0

Views: 78

Answers (1)

Squirrel
Squirrel

Reputation: 24763

use a CASE ... WHEN statement

SELECT KLANT_NR, SUM(PRIJS) AS OMZET,
    CASE WHEN SUM(PRIJS) >= 150 THEN 'GOLD'
         WHEN SUM(PRIJS) >= 100 THEN 'SILVER'
         WHEN SUM(PRIJS) >=  50 THEN 'BRONZE'
         END
FROM ( ....

add it to your query like above as one of the select element

Upvotes: 2

Related Questions