Help with Dates (SQL/mySQL)

[xvpindqrjqkk]

Hi,

I've got a table of employees called "employee" and a table of worked shifts called "job".

"job" contains a column called "startdate" that contains a mySQL datetime field in the form 0000-00-00 00:00:00. The are linked by employeeid - primary key or "employee" and a fireign key in "job".

What I'm hoping to do is use this information to select all employeeid's from "employee" where the "job" table shows they have had 6 or less working days in the last 10 days.

Any suggestions anyone? I'm stumped!

 

[rac2]

This is a case of the problem of aggregate and detail data. Make a view or a subquery that yields the aggregate data you need plus the key column. Then join that to the detail table.

This is the aggregate query.

SELECT employee, COUNT(*) AS "DaysWorkedInPast10"
FROM job
WHERE startdate BETWEEN DATEADD(day, -10, getdate()) AND getdate()
GROUP BY employee
HAVING COUNT(*) < 6

DATEADD and getdate() are SQL Server functions, you must substitute the corresponding mySQL functions.

Suppose you use that to CREATE VIEW EmployeesUnder6Days. Then use it in a JOIN to get all the info about those employees.

SELECT e.*, s.DaysWorkedInPast10
FROM employee e
JOIN EmployeesUnder6Days s ON
   e.employeeid = s.employee

Presto!

 

[r937]

mysql version:

[tt]select employeeid
, count(*) as DaysWorkedInPast10
from job
where startdate between
date_add(current_date, interval -10 day)
and current_date
group
by employeeid
having count(*) <= 6[/tt]

rudy

http://r937.com/

SQL Consulting