Hi,

I'm really stuck - I'm using mySQL 3.2 and am trying to write an SQL statement to allocate work to employees more fairly.

I have a table of employees called "emlpoyee" with the primary key "employeeid".

I have a table of past work called "job" which has employeeid as a foreign key and a mySQL datetime filed (0000-00-00 00:00:00) showing when a job began.

What I'd like to do is select all "employeeid"s from "employee" where "job" shows they have had at least 3 day without a job of in the last 10.

(The numbers might change, but I'm sure I can hack that later!)

Any ideas on how to go about this?

RE: Any ideas on how to go about this?

Hi, Don! I'm entirely the wrong person to answer this since I am not experienced and just created my first mySQL db yesterday and cannot write even a basic SQL query yet, but since no one else has answered yet, I'll throw some stuff out there and maybe it'll help you or someone more capable than I...

Since your 10-day period will be constantly changing and you need to check all employees, what if you create an INT field w/a constant value =1 to represent 1 day worked and and then search for all employees jobs in the past 10 days, sort by employee, and display a sub-summary calculation for each employee that totals the 1 day worked fields?

Sorry I can't SQL-talk yet, but FWIW, that's the logic I might pursue if it were my own project. I don't even know if mySQL has sub-summary calcs but I would think so. Hope this leads to something.
--ST

Hmm.. no one's improved on this or listed commands? I'm surprised no one's said, "you gotta be kiddding... this is the way to do it..." or something. C'mon folks, DonWho needs help!
--ST