0

How do I show NULL value = 0 

SELECT
    d.Date,
    a.EmployeeID,
    a.GivenName, a.FamilyName,
    a.TeamID,
    d.ShiftType, d.ShiftDuration,
    b.Duration_Off,
    c.OT_Duration,
    (d.ShiftDuration + c.OT_Duration) - b.Duration_Off as Total_Hours
FROM 
    Employee a
INNER JOIN 
    Roster d ON  a.EmployeeID = d.EmployeeID 
LEFT JOIN 
    Leave b ON a.EmployeeID = b.EmployeeID 
LEFT JOIN 
    Overtime c ON  a.EmployeeID = c.EmployeeID

Result of query when ran.

As my Duration_Off and OT_Duration might be NULL at times, how do I show 0 when it's NULL?

Improve this question
0

1 Answer 1

Reset to default
0

Use coalesce():

select . . .,
       coalesce(d.ShiftDuration, 0) as ShiftDuration,
       coalesce(b.Duration_Off, 0) as Duration_Off,
       coalesce(c.OT_Duration, 0) as OT_Duration
       (coalesce(d.ShiftDuration, 0) + coalesce(c.OT_Duration, 0) - coalesce(b.Duration_Off, 0)
       ) as Total_Hours

For the arithmetic to work correctly, you need coalesce() on each component of the sum for Total_Hours.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments