CODEWITHSUNDEEP
sqlsql-servert-sql
Hackawar
Hackawar

Reputation: 25

IIF with ISNULL?

    SELECT first_name, last_name
    (SELECT  ISNULL(IIF(getdate() between vacation_start and vacation_end, 1, 0),0) from vacatoin_request where mat_emp = W.mat_emp) as is_on_vacation,
    (SELECT  ISNULL(IIF(getdate() between mission_start and mission_end, 1, 0),0) from mission_order where mat_emp = W.mat_emp) as is_on_mission
    FROM
    workers W

IIF is working fine but when I add ISNULL, It's still returning null If that worker have no vacation requests or mission orders in the other tables.

Upvotes: 0

Views: 4203

Answers (3)

dimo raichev
dimo raichev

Reputation: 580

The problem is the select returns NULL so ISNULL needs to be outside the select

 SELECT first_name, last_name
    ISNULL((SELECT TOP 1 IIF(getdate() between vacation_start and vacation_end, 1, 0) from vacatoin_request where mat_emp = W.mat_emp),0) as is_on_vacation,
    ISNULL((SELECT TOP 1 IIF(getdate() between mission_start and mission_end, 1, 0) from mission_order where mat_emp = W.mat_emp),0) as is_on_mission
FROM
workers W

Upvotes: 1

Vlam
Vlam

Reputation: 1798

SELECT first_name, last_name
    (SELECT COUNT(DISTINCT v.mat_empt) from vacatoin_request v where v.mat_emp = W.mat_emp and getdate() between vacation_start and vacation_end) as is_on_vacation,
    (SELECT COUNT(DISTINCT m.mat_emp) from mission_order m where m.mat_emp = W.mat_emp and getdate() between mission_start and mission_end) as is_on_mission
    FROM
    workers W

Upvotes: 0

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

SELECT 
    first_name, last_name
    CASE WHEN v.mat_emp IS NOT NULL THEN 1 ELSE 0 END   as is_on_vacation
    CASE WHEN m.mat_emp IS NOT NULL THEN 1 ELSE 0 END   as is_on_mission
FROM
    workers         W   
LEFT JOIN 
    vacatoin_request    v
ON 
    v.mat_emp = W.mat_emp       AND
    getdate() between v.vacation_start and v.vacation_end
LEFT JOIN 
    mission_order       m   
ON
    m.mat_emp = W.mat_emp       AND
    getdate() between m.mission_start and m.mission_end

Upvotes: 0

Related Questions