Little Employee/Shift timetable HELP!!!

Question

Morning Guys,

I have the following tables:

 operator(ope_id, ope_name)
 ope_shift(ope_id, shift_id, shift_date) 
 shift(shift_id, shift_start, shift_end)

here is a better view of the data http://latinunit.net/emp_shift.txt

here is the screenshot of a select statement to the tables http://img256.imageshack.us/img256/4013/opeshift.jpg

im using this code

SELECT OPE_ID, COUNT(OPE_ID) AS Total_shifts
from operator_shift
group by ope_id;

to view the current total shifts per operator and it works, BUT if there was 500 more rows it would count them all aswell, THE QUESTION is, anyone has a better way of making my database work, or how can i tell the system that those rows are a whole month, i remember i friend said something about count then devide by 30 but im not sure, what if the month isnt finished? and you want to show the emp with highest shifts to date

Answer 1

you need to add a where clause:

where shift_date in ('2010-04-01', '2010-04-30')

or alternately

where (shift_date >= '2010-04-01' AND shift_date < '2010-05-01')

You might need to fudge the date format to match your particular implementation.