ansaurus

Question

MySQL query to get non-data for unassigned dates

Answer 1

A:

Joel Meador 2009-07-13 23:05:26

Wow, thank you for spotting that. Unfortunately, the service account I am set up with for the DB doesn't have temporary table privs. But I'll see if I can't pull teeth to change that. That looks like the perfect solution.

Anthony 2009-07-14 03:04:56

If you don't have tempo privileges, you can just make a permanent table -- you might want to anyway if you use these queries a lot -- that contains all the dates for the 60y around today's date or something. Change that number based on your needs.

Joel Meador 2009-07-14 07:24:27

That seems so silly, just having a table of dates, but it may come to that. This is why I thought there would be a function already in place, to avoid making utility tables like that. Ah well. So I would do a left join of two queries then? One query for the data, one for the date range, right?

Anthony 2009-07-14 19:10:30

Yeah, it kind of sucks, but sql is about querying existing data. If you want it pure sql, there isn't another way to do it.How you end up getting the end result isn't obvious, as I'm not really sure what it will look like. There will definitely be some left join in there. :)

Joel Meador 2009-07-15 03:01:21

So the idea is this:First there is a query that gets all the user names. Then there is a foreach loop that queries the weekly schedule for each user. But to make it look consistent (thus avoiding the "Oh, I thought it was on Tuesday but I have Tuesday off!") the missing days are filled in. It also needs to do a nested conditional query to find out if ANY of the users are scheduled for a weekend date, so that the date range extends the extra two days so that even if a user has that weekend off, they can know for sure.

Anthony 2009-07-15 06:14:08

Answer 2

A:

Easy! Flip the query around!

That is:

Create a lookup table with the days of the week
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 ...

A ROW for ALL DAYS
In the row, UserIDs will exist for Non Blank Days and be null for days skipped

CMB 2009-07-14 05:00:17

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 2009-07-14 06:55:00

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 2009-07-14 18:01:38

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 2009-07-15 06:10:23

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 2009-07-15 13:50:09

ansaurus

tags:

views:

answers:

MySQL query to get non-data for unassigned dates

related questions