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,
Wednesday, September 17, 2008 4:04 AM
this is my table structure
and here is my query,
SELECT hours_worked from tbl_attendence_log where max(id) in (select max(id) from tbl_attendence_log where emp_id=1)
if i run this query i get error as "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."
1.how do i get the hours_worked having only the emp_id where the id col has the max value.?
2. how can i update the hours_worked column using a trigger as soon the emp_checkout is updated or inserted ?
thansk your ur kindly reply
Wednesday, September 17, 2008 4:54 AMModerator
Could you give some example data and result which you want?
More details for us can make a better help.
Wednesday, September 17, 2008 5:18 AM
SELECT hours_worked from tbl_attendence_log where id in (select max(id) from tbl_attendence_log where emp_id=1)
Saturday, June 02, 2012 1:50 PM
i think this link helpful 4 u
Sunday, June 03, 2012 5:32 AMModerator
select top (1) hours_worked from tbl_attendence order by ID DESC -- hours of worked of max ID
I think you may want to use calculated column for hours worked.
If you want to use trigger, I think it will be
create trigger trUpdateHoursWorked on tbl_attendence AFTER INSERT, UPDATE
update T set hours_worked = datediff(hour, emp_checkin, emp_checkout)
from tbl_attendence T inner join inserted I ON T.ID = I.ID where I.Checkout IS NOT NULL
For every expert, there is an equal and opposite expert. - Becker's Law
- Proposed As Answer by Syed Qazafi AnjumMicrosoft Community Contributor Sunday, June 03, 2012 5:51 AM
Sunday, June 03, 2012 10:25 PMPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
>> this is my table structure <<
If you had basic Netiquette and knew RDBMS, this is what you would have post instead of being rude. Notice that we do not need a magical generic “id” or a computed column in a correct schema. Also the silly “tbl-” prefix is not only a violation of ISO-11179 rules, but one of the fastest ways to find a bad SQL programmers.
CREATE TABLE Attendance_Log
(emp_id CHAR(10) NOT NULL,
DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, emp_checkin_timestamp),
CHECK (emp_checkin_timestamp < emp_checkout_timestamp));
Guessing at the specifications you also did not post
1.how do I get the most recent hours_worked for each employee? The current session is found with (emp_checkout_timestamp IS NULL)
SELECT emp_id, emp_checkout_timestamp,
FROM Attendance_Log AS A1
= (SELECT MAX( emp_checkout_timestamp)
FROM Attendance_Log AS A2
WHERE A1.emp_id = A2.emp_id);
>> how can I update the hours_worked column using a trigger as soon the emp_checkout is updated or inserted ? <<
You do not!! This is SQL and not punch cards; You compute it in a VIEW, query or computed columns. You should write fewer than five triggers in your entire career.
Stop programming until you have a basic education; you have n idea what you are doing.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL