Hi Everyone,

I'm very new to databases and spent weeks learning and thinking about how to write a search to get what I want, and finally I did it! Terribly written with nested SELECT statements but it works.

I figured I would just use that since my database is pretty small, starting at like 200 rows. Well now I'm up to 600 rows and my .php page is timing out when trying to query, and I think it's because of my crappy code, because my query still runs when I manually enter it into phpMyAdmin.

I have two tables, one keeps track of employees and what days they take off from work. This table is called 'misloadDaysOff', and here is some example rows:

employee dayOff
DerpA 2011-10-18 05:09:23
DerpB 2011-10-18 05:09:23
DerpB 2011-10-18 05:09:23

The second table is called 'misload' and keeps track of the employee who misloaded, the timestamp when it happened, and other data related to the event that is not relevant to the query I'm trying to clean up.

Here's some example rows from 'misload' (removing the irrelevant columns):

employee timestamp
DerpA 2011-10-28 10:39:35
DerpB 2011-10-29 10:39:42
DerpA 2011-10-29 10:39:47

So as you may have realized, an employee having a 'misload' is a bad thing. At my work, as an attempt to inspire people to work smarter, I have made my SELECT query to list all employees and how many days it has been since their most recent misload. My query also has to take into account days that employees didn't work, because if DerpA takes 3 weeks off work, we don't want to give him credit for not making any mistakes for those 3 weeks.

First I'll show you an output from my select statement:

currentEmployee daysWithoutMisload daysOffSubtracted
DerpA 53 3
DerpB 28 6
DerpC 22 1
DerpD 11 8
DerpE 7 1
DerpF 5 0

So DerpA is winning this competition with 53 working days without a misload.

DerpD has had 11 working days without a misload, after taking out 8 days off in between.

Hopefully this still makes sense to everyone. Now I suppose I have to show you my embarrasing, terrible select statment. I only have basic SQL knowledge. I've tried improving this on my own, and I'm guessing INNER JOIN is what I need to use, but I haven't been able to successfully. Here's my code, and I'll explain it at the bottom:


"SELECT employee AS currentEmployee, (

SELECT (
(

SELECT COUNT( DISTINCT DATE( timestamp ) )
FROM misload
WHERE DATE( timestamp )
BETWEEN (

SELECT MAX( DATE( timestamp ) )
FROM misload
WHERE employee = currentEmployee
)
AND NOW( )
) - 1 - (

SELECT COUNT( * )
FROM misloadDaysOff
WHERE employee = currentEmployee
AND dayOff
BETWEEN (

SELECT MAX( DATE( timestamp ) )
FROM misload
WHERE employee = currentEmployee
)
AND NOW( )
)
)
) AS daysWithoutMisload, (

SELECT COUNT( * )
FROM misloadDaysOff
WHERE employee = currentEmployee
AND dayOff
BETWEEN (

SELECT MAX( DATE( timestamp ) )
FROM misload
WHERE employee = currentEmployee
)
AND NOW( )
) AS daysOffSubtracted
FROM misload
GROUP BY currentEmployee
ORDER BY daysWithoutMisload DESC , currentEmployee ASC"


Okay, so I will go through each employee, and first I count the distinct days between their most recent misload and NOW(). Their most recent misload is found with MAX( DATE( timestamp ) ). I count days using a query because I only want to count working days without a misload, not calendar days. There will be at least one entry in the 'misload' table for each working day, so counting distinct dates in the 'misload' table does get me a count of working days.

Then I take that count of days, and subtract the number of days off that employee has had within that same date range. I call that resulting number daysWithoutMisload. I'm sure you notice I also subtract '1'. Just trust me that this is necessary.

Then I repeat the second half of that previous query to get only the days off the employee had, and I call this 'daysOffSubtracted' (Some employees want to see this number.)

Finally, I group and sort the results, and get an output identical to the example output I listed above.

I've tried to arrange this code so that I don't have to repeat the query to get 'daysOffSubtracted', but I haven't been able to successfully.

Anyway, any and all help is appreciated

Thanks so much for your time,
Ben