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);