Recurring meeting on multiple days UGH!

WebRuss

Ugh!

I need to create some SQL that will generate all the dates for a recurring meeting on multiple days per week.

For example:
a meeting on Monday , Wednesday and Friday at 10:00 am - 10:30 am from December 1st 2004 to Feb 28th 2005.

I'm not sure what to do here.

Any Ideas?

Regards
Russ

AstroTeg

I was faced with a coding task like this at one time. I think I had a database mapped out, but I got assigned to a hotter project then this so I never got it fully working.

You'll have to design a method of storing a meeting in the database in such a way you can keep track of the meeting attributes (such as recurring on MWF). I used bit masking for the days of the week. I stored the start date and end date (if the meeting was a one day thing, then start and end dates would be the same).

The masking might work like this:

Monday: 1
Tuesday: 2
Wednesday: 4
Thursday: 8
Friday: 16
Saturday: 32
Sunday: 64

To get Monday, Wednesday, and Friday, you just add those bits all together: 1 + 4 + 8 = 13 would be the number you store with this meeting.

Displaying the data becomes another interesting exercise. If you were to check if the MWF meeting was today, you'd just take today's day mask and test it against the one you have stored. So lets say today was Tuesday. Then you'd do something like: 13 & 2. Does it equal to 2? If so, then that meeting is on today. If not, then you know the meeting is not today but another day (such as in this example).

Its tough to explain in a forum. But check out bit masking and using the & operator in PHP and the database engine you're using (databases usually love doing this kind of stuff).

I'm sure there's 200 other approaches then the one I just gave. This one is just an idea...

WebRuss

That is a great idea.

I'm trying to figure out how I would decode to number.

From your example:
Mon = 1
Tue = 2
Wed = 4
Thu= 8
Fri = 16
Sat = 32
Sun = 64

Lets say I have a TUE + THU Meeting
so I would have 2 + 8 = 10

How do I decode the 10 back into TUE and THU?

Regards
Russ

AstroTeg

You would have to do an and operation.

A ten looks like this in binary: 1010

Monday: 0001
Tuesday: 0010
Wednesday: 0100
Thursday: 01000
Friday: 010000
...

Monday and Tuesday: 0011
Tuesday and Thursday: 01010 (or 1010)

Each bit represents 1 day of the week. If you wish to find out all the days an event is good for, you'd have to decode the bits.

What's pretty cool about this is you can quickly scan your table for all events on a Tuesday. Or a Tuesday and Thursday or whatever you like. You would have to do a little decoding if you were to show 1 event and all its details and wished to show all the days the event was on. You might do something like:

function days($num)
{
	$sday = '';
	$days[1] = 'Monday';
	$days[2] = 'Tuesday';
	$days[4] = 'Wednesday';
	$days[8] = 'Thursday';
	$days[16] = 'Friday';
	$days[32] = 'Saturday';
	$days[64] = 'Sunday';

for($a = 1, $count = pow(2, count($days)); $a < $count; $a *= 2)
	if(($a & $num) == $a)
		$sday .= $days[$a] . ' ';

return $sday;
}  // end function days($num)

You can play with that above function and toss all sorts of numbers at it and it'll figure out which days that number is for.