SQL Server – Practice CASE Expression

The CASE expression is frequently used in day-to-day tasks, particularly when preparing reports and conducting data analyses. It analyzes a set of conditions and provides one of many possible result expressions.

When the first condition is satisfied, the CASE expression iterates over conditions and returns a value (like an if-then-else statement). Therefore, it will stop reading and return the outcome if a condition is true. It returns the value in the ELSE clause if none of the criteria are true.

One of the case scenarios that can be used to practice is shown below.

SQL Script for preparing the dataset:

CREATE TABLE Trxns(
TrxnId INT,
PaidAmount DECIMAL,
PaidDate SMALLDATETIME,
PaymentStatus VARCHAR(20)
);

INSERT INTO Trxns VALUES(1, 2000.00, ‘2020-01-16 13:10:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(2, 3300.00, ‘2020-01-17 13:33:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(3, 1100.00, ‘2020-01-21 15:01:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(3, 1100.00, ‘2020-01-21 15:01:00:000′,’DECLINED’);
INSERT INTO Trxns VALUES(4, 5400.00, ‘2020-01-22 15:49:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(5, 5200.00, ‘2020-01-23 16:30:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(6, 3400.00, ‘2020-01-23 16:59:00:000′,’REFUNDED’);
INSERT INTO Trxns VALUES(7, 1200.00, ‘2020-01-24 17:10:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(8, 4200.00, ‘2020-01-25 18:22:00:000′,’REFUNDED’);
INSERT INTO Trxns VALUES(9, 2100.00, ‘2020-01-28 18:43:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(10, 3400.00, ‘2020-01-30 19:12:00:000′,’DECLINED’);
INSERT INTO Trxns VALUES(11, 5500.00, ‘2020-01-31 22:19:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(12, 2200.00, ‘2020-02-01 13:10:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(13, 3900.00, ‘2020-02-03 13:33:00:000′,’REFUNDED’);
INSERT INTO Trxns VALUES(14, 1250.00, ‘2020-02-04 15:01:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(15, 4500.00, ‘2020-02-06 15:49:00:000′,’DECLINED’);
INSERT INTO Trxns VALUES(16, 4100.00, ‘2020-02-08 16:30:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(17, 3330.00, ‘2020-02-12 16:59:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(18, 1250.00, ‘2020-02-13 17:10:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(19, 4379.00, ‘2020-02-18 18:22:00:000′,’DECLINED’);
INSERT INTO Trxns VALUES(20, 2377.00, ‘2020-02-20 18:43:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(21, 3898.00, ‘2020-02-22 19:12:00:000′,’SUCCEEDED’);
INSERT INTO Trxns VALUES(22, 5555.00, ‘2020-02-27 22:19:00:000′,’SUCCEEDED’);

Requirement:
Each day, there are numerous transactions that are either accepted, rejected, or reimbursed. It is necessary to know how many transactions there are for each status.

Required Output:

iMonth iYear SUCCEEDED REFUNDED DECLINED
1 2020 8 2 2
2 2020 8 1 2

Here is the answer:

SELECT
MONTH(PaidDate) AS iMonth,
YEAR(PaidDate) AS iYear,
COUNT(CASE WHEN (PaymentStatus = ‘SUCCEEDED’)
THEN 1 ELSE NULL END)
AS SUCCEEDED,
COUNT(CASE WHEN (PaymentStatus = ‘REFUNDED’)
THEN 1 ELSE NULL END)
AS REFUNDED,
COUNT(CASE WHEN (PaymentStatus = ‘DECLINED’)
THEN 1 ELSE NULL END)
AS DECLINED
FROM Trxns
GROUP BY MONTH(PaidDate), YEAR(PaidDate);

Hope you find this post helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s