SQL - COALESCE

COALESCE (Transact-SQL)

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

CREATE TABLE dbo.wages 

( 

    emp_id        tinyint   identity, 

    hourly_wage   decimal   NULL, 

    salary        decimal   NULL, 

    commission    decimal   NULL, 

    num_sales     tinyint   NULL 

); 

GO 

INSERT dbo.wages (hourly_wage, salary, commission, num_sales) 

VALUES 

    (10.00, NULL, NULL, NULL), 

    (20.00, NULL, NULL, NULL), 

    (30.00, NULL, NULL, NULL), 

    (40.00, NULL, NULL, NULL), 

    (NULL, 10000.00, NULL, NULL), 

    (NULL, 20000.00, NULL, NULL), 

    (NULL, 30000.00, NULL, NULL), 

    (NULL, 40000.00, NULL, NULL), 

    (NULL, NULL, 15000, 3), 

    (NULL, NULL, 25000, 2), 

    (NULL, NULL, 20000, 6), 

    (NULL, NULL, 14000, 4); 

GO 

SET NOCOUNT OFF; 

GO 

SELECT CAST(COALESCE(hourly_wage * 40 * 52,  

   salary,  

   commission * num_sales) AS money) AS 'Total Salary'  

FROM dbo.wages 

ORDER BY 'Total Salary'; 

GO 

1.      Because ISNULL is a function, it's evaluated only once. The input values for the COALESCE expression can be evaluated multiple times.

2.      Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

3.     The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). By contrast, COALESCE with non-null parameters is considered to be NULL. So, the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values.

4.      Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

5.      ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.

SQL - MULTIPLE COUNT OR SUM CASE WHEN

Multiple COUNT 

SELECT Approved  = (SELECT COUNT(*) FROM dbo.Claims d

                    WHERE d.Status = 'Approved'),

        Valid    = (SELECT COUNT(*) FROM dbo.Claims d

                    WHERE d.Status = 'Valid'),

        Reject   = (SELECT COUNT(*) FROM dbo.Claims d

                    WHERE d.Status = 'Reject')

SUM CASE WHEN

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),

        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),

        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)

FROM dbo.Claims c;

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),

        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),

        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)

FROM dbo.Claims c

WHERE  c.Status = 'Approved'

    OR c.Status = 'Valid'

    OR c.Status = 'Reject';

SQL - Remove Duplicate With Inner Join

Create Table 

CREATE TABLE e (Emplyee_Dep_ID INT, Employee_Reg_Date DATE, Employee_Rep_Manager VARCHAR(100), Employee_Status INT, Email  VARCHAR(100));

INSERT INTO e

VALUES

(1,'2020-01-01',' Robot', 3,'a@a.com'),

(1,'2020-01-01',' Robot', 3,'b@a.com'),

(1,'2020-01-01',' Robot', 3,'c@a.com'),

(1,'2020-01-01',' Robot', 3,'d@a.com'),

(1,'2020-01-01',' Robot', 1,'e@a.com');

CREATE TABLE s  (Status_ID int, Status_Code varchar(100));

INSERT INTO s

VALUES

(3,'Promoted'),

(1,'Probation');

Remove Duplicate

SELECT *

from

(

    SELECT  emp.emplyee_dep_id,emp.employee_reg_date,emp.employee_rep_manager,

            employee_status as status_id

    FROM e emp

    INNER JOIN (SELECT

                    emplyee_dep_id,employee_reg_date,employee_rep_manager, 

                         COUNT(*) AS CountOf

                    FROM e

                    GROUP BY emplyee_dep_id,
                             employee_reg_date,
                             employee_rep_manager

                    HAVING COUNT(*)>1

                ) emp1

                ON emp.emplyee_dep_id=emp1.emplyee_dep_id

                AND emp.employee_reg_date=emp1.employee_reg_date

                AND emp.employee_rep_manager = emp1.employee_rep_manager

) E

INNER JOIN s s

ON E.status_id = s.status_id

where s.status_code = 'promoted'

Group by emplyee_dep_id,employee_reg_date,employee_rep_manager

SQL - Convert Hex To VarBinary To Int

---Convert Hex To VarBinary To Int

-- If the '0x' marker is present:

SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:

SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

--Hexadecimal

'4001802D'

--Convert Hexadecimal Into Hexadecimal Varbinary (eg.0x4001802D)

SELECT CONVERT(VARBINARY, '0x4001802D', 1)

SELECT CONVERT(VARBINARY, '4001802D', 2)

--Convert Hexadecimal Varbinary Into Integer (Binary In Decimal/Int) (eg.1073840173)

SELECT CONVERT(INT, convert(VARBINARY, '4001802D', 2))