In your
WHERE
clause you have
and (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108))
This is not a condition - you are not comparing it to anything. You need to do one of the following:
1. Finish off the clause by comparing the converted date to something e.g. using BETWEEN or < or >
OR
2. Remove that part of the WHERE clause altogether
---------------------------
[Edit]For the benefit of the curious or anyone experiencing a similar issue...
The OP appears to have changed the query to
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
AND (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108) <='02:00:00')
Which will result in the error
Quote:
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.
The problem is obviously that
SUM
which to be honest I hadn't spotted.
One solution is to use a HAVING clause:
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
HAVING (convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108) <='02:00:00')
or you can use a CTE and use the column alias in the
AND
clause
Select * from
(
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0), 108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
) temp
Where Short <='02:00:00'
[2nd suggestion posted at
How to use Alias in where cause in sql server[
^] by @RAHUL(10217975) - bug corrected by me]