ryanchapman
MIS
Hi everyone,
I have a simple question about a select statement that is-not-too-simple for me.
I have one sql table, named 'schedule' which stores
schedule info for all users. The table only stores
scheduling data for mon-fri, 8am-5pm. Its only a
prototype.
[tt]
+-------+---------+---------+---------+-----+---------+
|userid | mon0800 | mon0830 | mon0900 | ... | fri1700 |
+-------+---------+---------+---------+-----+---------+
| 1 | 5 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 2 | -1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 5 | 1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
[/tt]
If a user is available for meetings, then -1 is stored
in the column; otherwise, the userid of whoever the user has
scheduled a meeting with is stored in the column. In the
example above, userid '1' has a meeting on monday at 8:00am
with userid '5'. Userid '1' has no meetings scheduled for
any other times. Userid '2' has no meetings scheduled.
What I need to do is make a sql query that will tell
me the common times that two userid's have available for a
meeting (The timeslots they have in common that are equal to
-1).
So if I were to run the available times query for
userid's 1 and 5 on the above dataset, I would expect the
following result:
[tt]
+---------+---------+-----+---------+
| mon0830 | mon0900 | ... | fri1700 |
+---------+---------+-----+---------+
| -1 | -1 | | -1 |
+---------+---------+-----+---------+
[/tt]
Any ideas?
Thanks a bunch!
I have a simple question about a select statement that is-not-too-simple for me.
I have one sql table, named 'schedule' which stores
schedule info for all users. The table only stores
scheduling data for mon-fri, 8am-5pm. Its only a
prototype.
[tt]
+-------+---------+---------+---------+-----+---------+
|userid | mon0800 | mon0830 | mon0900 | ... | fri1700 |
+-------+---------+---------+---------+-----+---------+
| 1 | 5 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 2 | -1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
| 5 | 1 | -1 | -1 | ... | -1 |
+-------+---------+---------+---------+-----+---------+
[/tt]
If a user is available for meetings, then -1 is stored
in the column; otherwise, the userid of whoever the user has
scheduled a meeting with is stored in the column. In the
example above, userid '1' has a meeting on monday at 8:00am
with userid '5'. Userid '1' has no meetings scheduled for
any other times. Userid '2' has no meetings scheduled.
What I need to do is make a sql query that will tell
me the common times that two userid's have available for a
meeting (The timeslots they have in common that are equal to
-1).
So if I were to run the available times query for
userid's 1 and 5 on the above dataset, I would expect the
following result:
[tt]
+---------+---------+-----+---------+
| mon0830 | mon0900 | ... | fri1700 |
+---------+---------+-----+---------+
| -1 | -1 | | -1 |
+---------+---------+-----+---------+
[/tt]
Any ideas?
Thanks a bunch!