Skip to content

Instantly share code, notes, and snippets.

@ArCiGo

ArCiGo/1. Example. CASE...THEN.sql

Last active May 20, 2019 00:03
Show Gist options
  • Save ArCiGo/648911ce77534c8c48e4566c5b74bbd3 to your computer and use it in GitHub Desktop.
Save ArCiGo/648911ce77534c8c48e4566c5b74bbd3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1. Example. CASE...THEN.sql
SELECT ord.CustomerId,
cus.FirstName,
cus.LastName,
Payback =
CASE
WHEN SUM(ord.TotalAmount) >= 10000 AND SUM(ord.TotalAmount) < 20000
THEN
SUM(ord.TotalAmount) * 0.25
WHEN SUM(ord.TotalAmount) >= 20000 AND SUM(ord.TotalAmount) < 30000
THEN
SUM(ord.TotalAmount) * 0.30
WHEN SUM(ord.TotalAmount) >= 30000
THEN
SUM(ord.TotalAmount) * 0.40
WHEN SUM(ord.TotalAmount) < 10000
THEN
0
END
FROM [ORDER] ord
LEFT JOIN Customer cus ON ord.CustomerId = cus.Id
GROUP BY ord.CustomerId, cus.FirstName, cus.LastName;
GO
/** Output **/
CustomerId FirstName LastName Payback
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------
1 Maria Anders 0.0000
2 Ana Trujillo 0.0000
3 Antonio Moreno 0.0000
4 Thomas Hardy 3451.6250
5 Christina Berglund 8090.4450
6 Hanna Moos 0.0000
7 Frédérique Citeaux 4772.0000
8 Martín Sommer 0.0000
9 Laurence Lebihan 7155.2850
10 Elizabeth Lincoln 6782.3100
/** Checking total amount of CustomerId 4 **/
SELECT ord.CustomerId,
SUM(ord.TotalAmount) Total
FROM [ORDER] ord
WHERE ord.CustomerId = 4
/** Output **/
CustomerId Total
----------- ---------------------------------------
4 13806.50
GROUP BY ord.CustomerId
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment