Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Using COALESCE To Return First Non Null Expression in SQL Server

When doing database testing, sometimes, we need to obtain non null expressions from multiple columns in a table or in few tables by using joins. 

In the following example, the temp table (#a)  holds employee salary information.

 

It includes four columns with information about the yearly salary of the employees: the hourly wage, monthly_salary, semi_monthly_salary and bi_weekly_salary. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in the hourly wage, monthly_salary, semi_monthly_salary and bi_weekly_salary.

 

Use AdventureWorks2008
Go

CREATE TABLE #a
(
    employee_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    monthly_salary        decimal   NULL,
    Semi_monthly_salary decimal null,
    bi_weekly_salary    decimal   NULL,
);
GO
INSERT #a(hourly_wage, monthly_salary,Semi_monthly_salary,bi_weekly_salary)
VALUES
    (10.00, NULL, NULL, NULL),
    (15.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (25.00, NULL, NULL, NULL),
    (NULL, 5000.00, NULL, NULL),
    (NULL, 7000.00, NULL, NULL),
    (NULL, 8000.00, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, NULL, 2500, null),
    (NULL, NULL, 3500, null),
    (NULL, NULL, 4000, null),
    (NULL, NULL, 5000, null),
    (NULL, NULL, null, 2350),
    (NULL, NULL, null, 3000),
    (NULL, NULL, null, 3200),
    (NULL, NULL, null, 4000)
    
    ;
GO
SET NOCOUNT OFF;
GO
SELECT employee_id, CAST(COALESCE(hourly_wage * 40 * 52, monthly_salary*12,semi_monthly_salary*24,bi_weekly_salary*52) AS money) AS 'Total Salary' 
FROM #a
ORDER BY 'Total Salary';
GO

SQL Result

employee_id Total Salary
----------- ---------------------
1           20800.00
2           31200.00
3           41600.00
4           52000.00
5           60000.00
9           60000.00
10          84000.00
6           84000.00
7           96000.00
11          96000.00
12          120000.00
8           120000.00
13          122200.00
14          156000.00
15          166400.00
16          208000.00

(16 row(s) affected)