How Do I Cross Join The Date To A Selection?

I need a query that will have the date, a user's name, and one of twelve operation_id's for that date. eg operations 'id';'name'; '1';'LASER CUTTING' '2';'DEBURR' '3';'MACHINING' '

Solution 1:

You could create a date table like this http://www.techrepublic.com/blog/datacenter/simplify-sql-server-2005-queries-with-a-dates-table/326

Edit: Added stored procedure to generate dates:

DROPPROCEDURE IF EXISTS datePopulate;
DELIMITER $$
CREATEPROCEDURE datePopulate( startDate datetime, numDays int)
BEGINdeclare currDate datetime default startDate;
declare i intdefault1;

WHILE (i<=numDays) DO       

    INSERTINTO DateLookup(DateFull, fullYear, weekdayname) 
    VALUES(currDate, date_format(currDate, '%Y'), date_format(currDate, '%a'));
    SET i = i+1;
    SET currDate = DATE_ADD(currDate , INTERVAL1DAY);

END WHILE;

END $$
DELIMITER ;

Once procedure is created, it can be called like this:

CALL datePopulate('2011-01-01', 30);

This will populate the table with 30 days starting at 2011-01-01.

I didn't add all the columns in the insert statement. Should be pretty straight forward to add though using information from here.

Solution 2:

The simplest way is to have a pre-defined table with all the dates in the year, that will be 365 rows max. You can then simply use that table in your query selecting only rows between 2011-01-01 and NOW(). This will also mean that your query will have to do a lesser job by not creating a date table on every run.

Just another thought, though I'm not sure if you'll need this. If the intent is to have a date table for every year, for example in 2012 you would like a similar date table but with all dates from 2012, then you might consider storing only the date and month without the year.

Hope this makes sense.