Rookie needing pointed in the right direction

[dustybug]

Hi folks - I'm new to this having been landed in the deep end at work due to staffing problems. Forgive me if my technical terms aren't 100% correct...

I have written a query for our staff database which looks at one specific date and returns names,payroll numbers, and some other data for staff which were absent on that date.

What is the easiest way to go about altering this query to make it run on a range/list of dates?

I had only ever need to look at it for the previous week so all I did was run it 7 times and change the date every time but now I have to run it to return a list of the staff absent for every individual day in 2006. I really don't want to run this 365 times

We have one table of absences which contain the incremental absence ID number, the staff number of the person who had that absence, the start date of the absence and the end date of the absence.

What I tried to do was to adapt the query to pick the first date in the required range, go through each absence id and see if that date is between the recorded start and end dates then go on in a loop and do the same for the second date in the range...then the third...and so on.

I'm sure someone can give me a good idea on how to do this or point me in the direction of the MySQL equivalent of a FOR loop!

Thanks.

 

[BiggerBrother]

What you want is an order by statement, and then jsut change the date to show the whole year.

So your statement would look a little like this:

Select * from table where date_field > [start_of_year] and date_field < [end_of_year] order by date_field asc, staff_name

This will order everything by the date of absence, and then list all those staff absent on that day, before it moves on to the next day.

If you can be more specific with column names and your current sql query, I can probably give you the exact SQL statement you need.

Hope this helps

BB