CODEWITHSUNDEEP
sqlsql-server
Evil rising
Evil rising

Reputation: 442

An aggregate may not appear in the WHERE clause

SELECT distinct    t1.EmplID, t3.EmplName
        , t1.RecTime AS InTime
        , t2.RecTime AS [TimeOut]
        , t1.RecDate AS [DateVisited]
        , DATEDIFF(Hour,t1.RecTime, t2.RecTime) TotalHours
FROM  AtdRecord t1 INNER JOIN AtdRecord t2 
ON    t1.EmplID = t2.EmplID 
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime< t2.RecTime
inner join HrEmployee t3 ON t3.EmplID = t1.EmplID 
Order By EmplID, DateVisited

Error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Actually I am trying to retrieve the difference between 1st time entered in day and last time entered in day (office leaving and entering time difference to calculate work hours)

I also want to group it by EmpName if 1st problem solved

Upvotes: 0

Views: 985

Answers (1)

Brett Schneider
Brett Schneider

Reputation: 4103

[update]

simplified version

SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , DATEDIFF(Hour,min(t1.RecTime),max(t2.RecTime)) TotalHours
        , t1.RecDate AS [DateVisited]
FROM  AtdRecord t1 
INNER JOIN 
      AtdRecord t2 
ON    t1.EmplID = t2.EmplID 
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join 
      HrEmployee t3 
ON    t3.EmplID = t1.EmplID 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate

[old]
i think you should get min/max first and group by date/employee before getting the datediff:

with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , t1.RecDate AS [DateVisited]
FROM  AtdRecord t1 
INNER JOIN 
      AtdRecord t2 
ON    t1.EmplID = t2.EmplID 
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join 
      HrEmployee t3 
ON    t3.EmplID = t1.EmplID 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT    EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
from times
Order By EmplID, DateVisited

Upvotes: 1

Related Questions