Case - When - Then - Else Script Example in SQL Server

This example is use Northwind database in SQL Server.
Contoh perintah SQL berikut menggunakan database Northwind di SQL Server.

This query is to display grand total each order and the description of grand total according to sales target which is $500.
Query ini menampilkan grand total dari setiap order dan keterangan menurut target penjualan yaitu sebesar $500.

The query :
Query-nya :

select a.orderid,
c.firstname + ' ' + c.lastname as employeename,
sum(b.unitprice*b.quantity) as subtotal,
description =
case
when sum(b.unitprice*b.quantity) > 500 then 'over target'
when sum(b.unitprice*b.quantity) < 500 then 'under target'
when sum(b.unitprice*b.quantity) = 500 then 'on target'
else 'n/a'
end
from orders a
inner join [order details] b on a.orderid = b.orderid
inner join employees c on a.employeeid = c.employeeid
group by a.orderid, c.firstname + ' ' + c.lastname
order by a.orderid

Modification the query, for example; we want to know grand total each employee on August 1994 :
Modifikasi query untuk mengetahui grand total setiap pegawai pada bulan Agustus 1994 :

select c.firstname + ' ' + c.lastname as employeename,
sum(b.unitprice*b.quantity) as subtotal,
description =
case
when sum(b.unitprice*b.quantity) > 5000 then 'over target'
when sum(b.unitprice*b.quantity) < 5000 then 'under target'
when sum(b.unitprice*b.quantity) = 5000 then 'on target'
else 'n/a'
end
from orders a
inner join [order details] b on a.orderid = b.orderid
inner join employees c on a.employeeid = c.employeeid
where month(a.orderdate) = 8 and year(a.orderdate) = 1994
group by c.firstname + ' ' + c.lastname
order by c.firstname + ' ' + c.lastname

Credit :ilmubiner