CODEWITHSUNDEEP
sqlsql-server
user9178291
user9178291

Reputation:

How to filter out certain values after calculating

I have done a Query in SQL Database whereby it takes data from other table and adds them up. So how do I then filter the value that was calculated to like WHERE Total_Hour < 12

I can't seem to get the Total_Hour recognised 

SELECT 
    r.Date,
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID  ,
    e.ContactNo,
    r.ShiftID,
    COALESCE(s.Shift_Duration, 0) AS Shift_Duration,
    s.Shift_Timing,
    COALESCE(l.Duration_Off, 0) AS Duration_Off,
    COALESCE(o.OT_Duration, 0) AS OT_Duration,
    (COALESCE(s.Shift_Duration, 0) + COALESCE(o.OT_Duration, 0) - COALESCE(l.Duration_Off, 0)) AS Total_Hours
FROM 
    Employee e
    INNER JOIN
    Roster r ON  e.EmployeeID = r.EmployeeID 
    LEFT JOIN
    Leave l ON e.EmployeeID = l.EmployeeID AND r.Date = l.Date
    LEFT JOIN 
    Overtime o ON  e.EmployeeID = o.EmployeeID AND r.Date = o.Date
    INNER JOIN 
    ShiftType s ON r.ShiftID = s.ShiftID

    WHERE Total_Hours < '12'
ORDER BY Total_Hours ASC

Upvotes: 1

Views: 45

Answers (2)

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

You can't use that directly in where clause but you can use this one instead

WHERE
(COALESCE(s.Shift_Duration, 0) + COALESCE(o.OT_Duration, 0) - COALESCE(l.Duration_Off, 0)) < '12'

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Just Wrap the SELECT Inside a CTE or Inner Query and Do The Filter in that

;WITH CTE
AS
(
    SELECT 
       r.Date,
       e.EmployeeID,
       e.GivenName,
       e.FamilyName,
       e.TeamID  ,
       e.ContactNo,
       r.ShiftID,
       COALESCE(s.Shift_Duration, 0) AS Shift_Duration,
       s.Shift_Timing,
       COALESCE(l.Duration_Off, 0) AS Duration_Off,
       COALESCE(o.OT_Duration, 0) AS OT_Duration,
       (COALESCE(s.Shift_Duration, 0) + COALESCE(o.OT_Duration, 0) - COALESCE(l.Duration_Off, 0)) AS Total_Hours
    FROM 
       Employee e
       INNER JOIN
       Roster r ON  e.EmployeeID = r.EmployeeID 
       LEFT JOIN
       Leave l ON e.EmployeeID = l.EmployeeID AND r.Date = l.Date
       LEFT JOIN 
       Overtime o ON  e.EmployeeID = o.EmployeeID AND r.Date = o.Date
       INNER JOIN 
       ShiftType s ON r.ShiftID = s.ShiftID
)
SELECT
    *
    FROM CTE
    WHERE Total_Hours < '12'
ORDER BY Total_Hours ASC

Or Change your Where Part like this

WHERE (COALESCE(s.Shift_Duration, 0) + COALESCE(o.OT_Duration, 0) - COALESCE(l.Duration_Off, 0)) < '12'
ORDER BY (COALESCE(s.Shift_Duration, 0) + COALESCE(o.OT_Duration, 0) - COALESCE(l.Duration_Off, 0)) ASC

Upvotes: 1

Related Questions