-1

I have a table with weekly data that looks like:

userID       Site             date
------      ------           ------
Smith    Ferris Wheel       2009-07-13
Jones   Outerspaceland      2009-07-13
LChar   Ferris Wheel       2009-07-14
Smith     Underworld        2009-07-16
Jones     Fish Bowl         2009-07-17
Munson    Go-Go Tech        2009-07-16

Currently I have a PHP script that first gets the date range (Monday -- Friday), then does a query for each userID for that date range. Then, I loop through the results and check for gaps between dates. If there is gap, it outputs the date and the Site is listed as "Stay Home." It has to account for several days in a row (like if the user is only stationed on Monday and Friday that week), and it has to an extra step at the end of each week to make sure that if the user is staying home after Wednesday (thus having Thursday and Friday to fill in as gaps). So there are three checks it has to make, one for users not starting on Monday, one for gaps between days, and one for users ending before Friday...plus it has to account for days in a row for any of those situations.

The script basically creates an array of the date range to check against, and it has worked fine for months. HOWEVER

I was wondering, is there a MySQL function to have days off returned for each user? So that I can have it fill in "Stay Home" in the query and not have to run a funky script that takes forever to explain to other people working with me on my script?

Thanks!

Anthony
  • 36,459
  • 25
  • 97
  • 163

1 Answers1

0

Easy! Flip the query around!

That is:

  1. Create a lookup table with the days of the week
  2. Sculpt a query that performs a LEFT_OUTER_JOIN on the lookup table and your table DAYNAME(field). That is, it will return all rows from the lookup table and only those that match in your table,,,

I'm not sure if you want to filter by person .. the criteria would look like userid = 'person' or userid is null

The resulting data will look be ...

  1. A ROW for ALL DAYS
  2. In the row, UserIDs will exist for Non Blank Days and be null for days skipped
CMB
  • 456
  • 3
  • 6
  • If I have a table that is just Monday - Sunday that I could reuse over and over, is there a date function that will, based on the actual date range reverse those weekdays into actual dates? One of my goals is to have as much of, if not all of, my html actual returned by the SQL query using concat and whatnot, so while using your idea does the trick for finding missing days, it would be ideal if it returned those missing weekdays as the missing dates (wrapped up in the html tags etc). – Anthony Jul 14 '09 at 06:55
  • Yes, it is possible to get both. Does your table include only a weeks worth of data? Include in the query a calculated column that does a date calculation based up on the starting date (which you pass in). There is also the option of UNION on a small table with dates and grouping by to remove any duplicates. – CMB Jul 14 '09 at 18:01
  • IF by a week's worth of data you mean doe the table only have data for one specific week, no. If you mean does it only every query for one week's worth of data, usually, but not always, so no again. I think I'll have to go with Joel's suggestion of having a table of dates instead. I would much rather have a lighter table of just 7 values, but it seems that to really get everything I want without the luxury of temp tables, I'll have to just have a 365 * 20 value table with just every day of the year. – Anthony Jul 15 '09 at 06:10
  • A table of dates would work. If you didn't want to maintain date data, you could even probably fudge it with a table of partial dates...etc. or Temp tables... Good luck – CMB Jul 15 '09 at 13:50