how to match time ranges for multiple records in a query?

am 03.10.2005 20:25:57 von Mike

Hello,

I want to have have a table like this in Microsoft Access 2000:

id Autonumber
userID Number
timeStart Date/Time
timeEnd Date/Time

where I can store time ranges for which users can meet for a
presentation. For example, a user, Mike, could have the following time
ranges in which he is available to meet:

12:00 to 1:00 on 10/5/05
14:30 to 15:00 on 10/5/05
18:30 to 19:30 on 10/6/05

Other users would have different time ranges, some of which may overlap
with Mikes.

Using such information for many users, I want to create a query (for
Access 2000 initially but preferably would work on MS SQL) that matches
a maximum of X users that can meet at the same time. If there are more
than X users that can meet at the same time, then the query returns the
users with the most overlap in the particular time range that matches.

SELECT TOP X userID WHERE ????

Anyone know how to compose the SQL statement??

Thanks,
Mike

Re: how to match time ranges for multiple records in a query?

am 03.10.2005 21:18:17 von reb01501

mike wrote:
> Hello,
>
> I want to have have a table like this in Microsoft Access 2000:
>
> id Autonumber
> userID Number
> timeStart Date/Time
> timeEnd Date/Time
>
> where I can store time ranges for which users can meet for a
> presentation. For example, a user, Mike, could have the following
> time ranges in which he is available to meet:
>
> 12:00 to 1:00 on 10/5/05
> 14:30 to 15:00 on 10/5/05
> 18:30 to 19:30 on 10/6/05
>
> Other users would have different time ranges, some of which may
> overlap with Mikes.
>
> Using such information for many users, I want to create a query (for
> Access 2000 initially but preferably would work on MS SQL) that
> matches a maximum of X users that can meet at the same time. If
> there are more than X users that can meet at the same time, then the
> query returns the users with the most overlap in the particular time
> range that matches.
>
> SELECT TOP X userID WHERE ????
>
> Anyone know how to compose the SQL statement??
>

I don't think what you want is achievable in a single sql statement, but I
may be misunderstanding your requirements. A picture is worth a thousand
words. Show us some sample data in tabular format followed by the results
you wish the query to return from that sample data. The results should also
be in tabular format. Make sure the sample data covers both or your
conditions. And define what you mean by "most overlap".

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 03.10.2005 23:08:11 von Mike

Hi Bob,

Here's a sample table:

ID userID day timeStart timeEnd
1 1 MON 12:00 PM 01:00 PM
2 1 WED 03:00 PM 06:30 PM
3 2 MON 12:30 PM 01:30 PM
4 3 TUE 12:15 PM 01:00 PM
5 4 WED 05:00 PM 07:00 PM
6 5 THU 07:00 PM 11:00 PM
7 6 FRI 11:00 AM 01:30 PM
8 2 TUE 12:00 PM 02:00 PM
9 5 WED 04:00 PM 08:00 PM
10 2 WED 01:00 PM 10:00 PM

It's a lot easier to understand what I'm trying to do if I sort by

day:

ID userID day timeStart timeEnd
7 6 FRI 11:00 AM 01:30 PM
1 1 MON 12:00 PM 01:00 PM
3 2 MON 12:30 PM 01:30 PM
6 5 THU 07:00 PM 11:00 PM
4 3 TUE 12:15 PM 01:00 PM
8 2 TUE 12:00 PM 02:00 PM
2 1 WED 03:00 PM 06:30 PM
5 4 WED 05:00 PM 07:00 PM
9 5 WED 04:00 PM 08:00 PM
10 2 WED 01:00 PM 10:00 PM

userID's 1 and 2 overlap between 12:30 and 1:00 on MON
userID's 4 and 8 overlap between 12:15 and 1:00 on TUE
userID's 1,4,5,2 overlap between 4:00 and 6:30 on WED

I want to make a query that works in access 2000 and ms SQL that
returns the userIDs and the overlap range similar to the statements

above. Any idea how to do it?

-Mike

Re: how to match time ranges for multiple records in a query?

am 03.10.2005 23:25:27 von reb01501

mike wrote:
> Hi Bob,
>
> Here's a sample table:
>
> ID userID day timeStart timeEnd
> 7 6 FRI 11:00 AM 01:30 PM
> 1 1 MON 12:00 PM 01:00 PM
> 3 2 MON 12:30 PM 01:30 PM
> 6 5 THU 07:00 PM 11:00 PM
> 4 3 TUE 12:15 PM 01:00 PM
> 8 2 TUE 12:00 PM 02:00 PM
> 2 1 WED 03:00 PM 06:30 PM
> 5 4 WED 05:00 PM 07:00 PM
> 9 5 WED 04:00 PM 08:00 PM
> 10 2 WED 01:00 PM 10:00 PM
>
> userID's 1 and 2 overlap between 12:30 and 1:00 on MON
> userID's 4 and 8 overlap between 12:15 and 1:00 on TUE
> userID's 1,4,5,2 overlap between 4:00 and 6:30 on WED

None of this addresses the meaning of "maximum of X users that can meet at
the same time" or "maximum overlap"
>
> I want to make a query that works in access 2000 and ms SQL that
> returns the userIDs and the overlap range similar to the statements
>
> above. Any idea how to do it?
>
> -Mike

I'm still not clear about what data needs to be returned. Your original
question mentioned "maximum of X" so it sounds as if some sort of count is
needed?

Let me put it this way: Given that your table looks as above, you run the
query. What does the returned recordset look like? Or, more t the point:
what do you want the recordset to look like? Recordsets are tabular in
nature, so it's easier to visualize the requirements if you present the
desired output in that tabular format.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 02:35:43 von Mike

Hi Bob,

I am thinking now that the X part I was talking about could be a second
query on the set of data that the first query returns. So I am not
going to worry about it for now.

I am not quite sure if this is the best way to return the data, but I
don't know how else to deal with the fact that there can be a variable
number of userIds that match a particular time. Here's what I came up
with:

userID's 1 and 2 overlap between 12:30 and 1:00 on MON
userID's 4 and 8 overlap between 12:15 and 1:00 on TUE
userID's 1,4,5,2 overlap between 4:00 and 6:30 on WED

translates to a query result of this:

day timeStart timeEnd userID
MON 12:30 1:00 1
MON 12:30 1:00 2
TUE 12:15 1:00 4
TUE 12:15 1:00 8
WED 4:00 6:30 1
WED 4:00 6:30 4
WED 4:00 6:30 5
WED 4:00 6:30 2

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 02:37:22 von Mike

(ignore the maximum overlap and maximum X comments earlier-- sorry...)

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 03:07:48 von reb01501

mike wrote:
> Hi Bob,
>
> I am thinking now that the X part I was talking about could be a
> second query on the set of data that the first query returns. So I
> am not going to worry about it for now.
>
> I am not quite sure if this is the best way to return the data, but I
> don't know how else to deal with the fact that there can be a variable
> number of userIds that match a particular time. Here's what I came up
> with:
>
>
> userID's 1 and 2 overlap between 12:30 and 1:00 on MON
> userID's 4 and 8 overlap between 12:15 and 1:00 on TUE
> userID's 1,4,5,2 overlap between 4:00 and 6:30 on WED
>
> translates to a query result of this:
>
>
> day timeStart timeEnd userID
> MON 12:30 1:00 1
> MON 12:30 1:00 2
> TUE 12:15 1:00 4
> TUE 12:15 1:00 8
> WED 4:00 6:30 1
> WED 4:00 6:30 4
> WED 4:00 6:30 5
> WED 4:00 6:30 2

One last clarifictaion: I thought you said you had Date/Time fields. Did you
use day names to "clarify" your problem, and you actually store date and
time in these fields? i.e: #2005-10-03 12:00#, etc. Or is your table
structure really
ID userID day timeStart timeEnd

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 08:45:18 von Mike

I changed the structure to make it easier to comprehend (both for me
and you) what I was trying to do. Now there is a separate day of the
week field...

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 15:33:36 von reb01501

mike wrote:
> I changed the structure to make it easier to comprehend (both for me
> and you) what I was trying to do. Now there is a separate day of the
> week field...

So now there is no way to identify the week? The weekday field is of no help
at all.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 22:37:45 von Mike

Hi Bob,

the intention was that the time ranges would repeat each week. For
example, you have a meeting each week from 2 pm to 3 pm. The original
design having the specific date was a bad design because there was no
need for specific dates, only specific times for each particular day of
the week.

In short, here's the table of data:

ID userID day timeStart timeEnd
7 6 FRI 11:00 AM 01:30 PM
1 1 MON 12:00 PM 01:00 PM
3 2 MON 12:30 PM 01:30 PM
6 5 THU 07:00 PM 11:00 PM
4 3 TUE 12:15 PM 01:00 PM
8 2 TUE 12:00 PM 02:00 PM
2 1 WED 03:00 PM 06:30 PM
5 4 WED 05:00 PM 07:00 PM
9 5 WED 04:00 PM 08:00 PM
10 2 WED 01:00 PM 10:00 PM

and here's the result of the query I would like to get:

day timeStart timeEnd userID
MON 12:30 1:00 1
MON 12:30 1:00 2
TUE 12:15 1:00 4
TUE 12:15 1:00 8
WED 4:00 6:30 1
WED 4:00 6:30 4
WED 4:00 6:30 5
WED 4:00 6:30 2

Re: how to match time ranges for multiple records in a query?

am 04.10.2005 23:24:22 von reb01501

mike wrote:
> Hi Bob,
>
> the intention was that the time ranges would repeat each week. For
> example, you have a meeting each week from 2 pm to 3 pm. The original
> design having the specific date was a bad design because there was no
> need for specific dates, only specific times for each particular day
> of the week.
>
> In short, here's the table of data:
>
> ID userID day timeStart timeEnd
> 7 6 FRI 11:00 AM 01:30 PM
> 1 1 MON 12:00 PM 01:00 PM
> 3 2 MON 12:30 PM 01:30 PM
> 6 5 THU 07:00 PM 11:00 PM
> 4 3 TUE 12:15 PM 01:00 PM
> 8 2 TUE 12:00 PM 02:00 PM
> 2 1 WED 03:00 PM 06:30 PM
> 5 4 WED 05:00 PM 07:00 PM
> 9 5 WED 04:00 PM 08:00 PM
> 10 2 WED 01:00 PM 10:00 PM
>
> and here's the result of the query I would like to get:
>
> day timeStart timeEnd userID
> MON 12:30 1:00 1
> MON 12:30 1:00 2
> TUE 12:15 1:00 4
> TUE 12:15 1:00 8
> WED 4:00 6:30 1
> WED 4:00 6:30 4
> WED 4:00 6:30 5
> WED 4:00 6:30 2

OK, that helps. I assume the time fields are date/time fields rather than
text, correct?
I'm probably not going to be able to get to this until tomorrow. Post a
reminder if I haven't replied by tomorrow evening. I think there's a way to
do this but I need to play with it a while.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 05.10.2005 19:56:15 von Mike

Yes, Date fields is correct

ID userID day timeStart timeEnd
7 6 FRI 11:00 AM 01:30 PM
1 1 MON 12:00 PM 01:00 PM
3 2 MON 12:30 PM 01:30 PM
6 5 THU 07:00 PM 11:00 PM
4 3 TUE 12:15 PM 01:00 PM
8 2 TUE 12:00 PM 02:00 PM
2 1 WED 03:00 PM 06:30 PM
5 4 WED 05:00 PM 07:00 PM
9 5 WED 04:00 PM 08:00 PM
10 2 WED 01:00 PM 10:00 PM

and here's the result of the query I would like to get:

day timeStart timeEnd userID
MON 12:30 1:00 1
MON 12:30 1:00 2
TUE 12:15 1:00 4
TUE 12:15 1:00 8
WED 4:00 6:30 1
WED 4:00 6:30 4
WED 4:00 6:30 5
WED 4:00 6:30 2

First off, you can easily find potential meetings from just sorting the
data by the day of the week. Then the only challenge is finding
matching times.
I was thinking that times are basically numbers. Looking at it that
way, the problem seems simpler. If you arbitrarily take one of the
time ranges, all you have to do is check each other record for the same
day of the week that has its start time >= the start time of the one
you chose AND <= the end time you chose. This will produce a list of
records that have overlapping times. Then, the time range when ALL
participants can participate in the meeting would simply be the
greatest start time and the least end time. OK that is the logic. How
to put this into SQL, I have no idea...

Re: how to match time ranges for multiple records in a query?

am 05.10.2005 20:07:41 von reb01501

Re: how to match time ranges for multiple records in a query?

am 08.10.2005 20:23:02 von reb01501

mike wrote:
> Yes, Date fields is correct
>
> ID userID day timeStart timeEnd
> 7 6 FRI 11:00 AM 01:30 PM
> 1 1 MON 12:00 PM 01:00 PM
> 3 2 MON 12:30 PM 01:30 PM
> 6 5 THU 07:00 PM 11:00 PM
> 4 3 TUE 12:15 PM 01:00 PM
> 8 2 TUE 12:00 PM 02:00 PM
> 2 1 WED 03:00 PM 06:30 PM
> 5 4 WED 05:00 PM 07:00 PM
> 9 5 WED 04:00 PM 08:00 PM
> 10 2 WED 01:00 PM 10:00 PM
>
>
> and here's the result of the query I would like to get:
>
>
> day timeStart timeEnd userID
> MON 12:30 1:00 1
> MON 12:30 1:00 2
> TUE 12:15 1:00 4
> TUE 12:15 1:00 8
> WED 4:00 6:30 1
> WED 4:00 6:30 4
> WED 4:00 6:30 5
> WED 4:00 6:30 2
>
> First off, you can easily find potential meetings from just sorting
> the data by the day of the week. Then the only challenge is finding
> matching times.
> I was thinking that times are basically numbers. Looking at it that
> way, the problem seems simpler. If you arbitrarily take one of the
> time ranges, all you have to do is check each other record for the
> same day of the week that has its start time >= the start time of the
> one you chose AND <= the end time you chose. This will produce a
> list of records that have overlapping times. Then, the time range
> when ALL participants can participate in the meeting would simply be
> the greatest start time and the least end time. OK that is the
> logic. How to put this into SQL, I have no idea...

Are you still here? Sorry for the delay, but real-life intruded.
The good news is that I have come up with a solution. The first step is to
create a table called TimeBlocks consisting of a single date/Time column
called TimeBlock. Put 96 records in this table: #12:00 AM#, #12:15 AM#, ...,
#11:45 PM#

Now, create a saved query called "TimeblocksPerPerson" using this sql (I
called the table containing your sample data "Schedules" - substitute your
table's name):

SELECT s.*, timeblock
FROM TimeBlocks AS b INNER JOIN Schedules AS s
ON (b.timeblock>=dateadd("s",-1,s.timeStart))
AND (b.timeblock ORDER BY [day], ID, timeblock

In SQL Server, you would create a similarly-named view with this sql (in
T-SQL, you will need to get rid of the quotes in the dateadd arguments).

Next create another saved query called "MultiReservedTimeblocks" using this
sql:

SELECT t.day, t.timeblock, Count(t.ID) AS CountOfID
FROM TimeblocksPerPerson AS t
GROUP BY t.day, t.timeblock
HAVING Count(t.ID)>1

Again, in SQL Server, you would create a similarly-named view with that sql.

Now, use this sql statement to retrieve your desired resultset:

SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
FROM TimeblocksPerPerson AS t INNER JOIN
MultiReservedTimeblocks AS m
ON (t.day = m.day) AND (t.timeblock = m.timeblock)
ORDER BY t.day, t.timeStart

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 09.10.2005 05:07:10 von Mike

I can't wait to try this!! Is T-SQL the one that runs in Access 2000?
Also, I was looking on aspfaq.com but I couldnt find the answer to this
question: what are CALs? Client Access Licenses is what I read it
means, but if I want to buy a copy of sql server on ebay, I see ones
with 5 and 10 CALs- what's the difference?

Re: how to match time ranges for multiple records in a query?

am 09.10.2005 13:25:05 von reb01501

mike wrote:
> I can't wait to try this!! Is T-SQL the one that runs in Access 2000?

No. T-SQL (short for Transact-SQL) is the variant of SQL that is used in MS
SQL Server. The variant of SQL that is used in Jet is called JetSQL.

One of the differences is that Jet can use various intrinsic VBA functions
(such as Instr, Iif, dateadd, etc.)
T-SQL on the other hand has many functions built into the language. These
functions have their own syntax, argument datatypes, etc. The Date...
functions are one example where you can see the difference. If you look up
dateadd() in VBA online help, you will see that it takes 3 arguments, the
first of which is a string argument, and therefore needs to be surrounded
with quotes when supplying a literal value:

x=dateadd("n",15,now())

In T-SQL, an error will be raised if the first argument is surrounded by
quotes. This is the correct usage in T-SQL:

set @x = dateadd(n,15,GETDATE())

You will need to familiarize yourself with SQL Books Online (BOL) if you are
going to be doing anything with SQL Server. BOL will be found in the SQL
Server program group in the Windows Start menu after SQL Server is installed
on a machine. If you have yet to install SQL Server, you can get a
standalone copy of BOL here:
http://msdn.microsoft.com/SQL/downloads/updates/default.aspx

> Also, I was looking on aspfaq.com but I couldnt find the answer to
> this question: what are CALs? Client Access Licenses is what I read
> it means, but if I want to buy a copy of sql server on ebay, I see
> ones with 5 and 10 CALs- what's the difference?

You will need to ask a MS sales representative this queston, unless you are
good at reading lawyer-speak, in which case you can read about the
differences at www.microsoft.com/sql (see the "How to buy" section)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 28.10.2005 21:24:51 von Mike

Hi Bob,

I am trying to get this code to work but I get a different result. I
am using Access 2000. Here's what I get when I put the final SQL in a
query and run it:

userID day timeStart timeEnd
1 MON 12:00:00 PM 1:00:00 PM
2 MON 12:30:00 PM 1:30:00 PM
2 TUE 12:00:00 PM 2:00:00 PM
3 TUE 12:15:00 PM 1:00:00 PM
2 WED 1:00:00 PM 10:00:00 PM
1 WED 3:00:00 PM 6:30:00 PM
5 WED 4:00:00 PM 8:00:00 PM
4 WED 5:00:00 PM 7:00:00 PM

I suspect it is because I am using Access 2000 and not SQL server like
you. Do you know what changes I need to make to your code to get it to
run properly in Access 2000?

Thanks,
Mike

Re: how to match time ranges for multiple records in a query?

am 28.10.2005 21:35:28 von reb01501

mike wrote:
> Hi Bob,
>
> I am trying to get this code to work but I get a different result. I
> am using Access 2000. Here's what I get when I put the final SQL in a
> query and run it:
>
> userID day timeStart timeEnd
> 1 MON 12:00:00 PM 1:00:00 PM
> 2 MON 12:30:00 PM 1:30:00 PM
> 2 TUE 12:00:00 PM 2:00:00 PM
> 3 TUE 12:15:00 PM 1:00:00 PM
> 2 WED 1:00:00 PM 10:00:00 PM
> 1 WED 3:00:00 PM 6:30:00 PM
> 5 WED 4:00:00 PM 8:00:00 PM
> 4 WED 5:00:00 PM 7:00:00 PM
>
>
> I suspect it is because I am using Access 2000 and not SQL server like
> you.

No, I was using Access.

It's been a while since I did this so I need to go back and refresh my
memory ... yeah. All I can say is that I got the correct results with the
data you provided. Are you using different data?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 28.10.2005 23:05:52 von Mike

no, same data. I sent you the mdb file to your other email address

Re: how to match time ranges for multiple records in a query?

am 31.10.2005 19:31:12 von Mike

Hi Bob,

Here's what I get when I put this:
SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
FROM TimeblocksPerPerson AS t INNER JOIN
MultiReservedTimeblocks AS m
ON (t.day = m.day) AND (t.timeblock = m.timeblock)
ORDER BY t.day, t.timeStart

in a stored procedure in access and run it:

userID day timeStart timeEnd
1 MON 12:00:00 PM 1:00:00 PM
2 MON 12:30:00 PM 1:30:00 PM
2 TUE 12:00:00 PM 2:00:00 PM
3 TUE 12:15:00 PM 1:00:00 PM
2 WED 1:00:00 PM 10:00:00 PM
1 WED 3:00:00 PM 6:30:00 PM
5 WED 4:00:00 PM 8:00:00 PM
4 WED 5:00:00 PM 7:00:00 PM

Here's what I was hoping to get:

day timeStart timeEnd userID
MON 12:30 1:00 1
MON 12:30 1:00 2
TUE 12:15 1:00 4
TUE 12:15 1:00 8
WED 4:00 6:30 1
WED 4:00 6:30 4
WED 4:00 6:30 5
WED 4:00 6:30 2

The big difference is that the second list has the times for the
overlap in each record, but for some reason I dont get this result...
any ideas what I am doing wrong?
Thanks,
Mike

Re: how to match time ranges for multiple records in a query?

am 31.10.2005 20:49:20 von reb01501

mike wrote:
> Hi Bob,
>
> Here's what I get when I put this:
> SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
> FROM TimeblocksPerPerson AS t INNER JOIN
> MultiReservedTimeblocks AS m
> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
> ORDER BY t.day, t.timeStart
>
> in a stored procedure in access and run it:
>
> userID day timeStart timeEnd
> 1 MON 12:00:00 PM 1:00:00 PM
> 2 MON 12:30:00 PM 1:30:00 PM
> 2 TUE 12:00:00 PM 2:00:00 PM
> 3 TUE 12:15:00 PM 1:00:00 PM
> 2 WED 1:00:00 PM 10:00:00 PM
> 1 WED 3:00:00 PM 6:30:00 PM
> 5 WED 4:00:00 PM 8:00:00 PM
> 4 WED 5:00:00 PM 7:00:00 PM
>
> Here's what I was hoping to get:
>
> day timeStart timeEnd userID
> MON 12:30 1:00 1
> MON 12:30 1:00 2
> TUE 12:15 1:00 4
> TUE 12:15 1:00 8
> WED 4:00 6:30 1
> WED 4:00 6:30 4
> WED 4:00 6:30 5
> WED 4:00 6:30 2
>
> The big difference is that the second list has the times for the
> overlap in each record, but for some reason I dont get this result...
> any ideas what I am doing wrong?
> Thanks,
> Mike

I think you are confusing id's. For example, there is no record with a user
id of 8 in the sample data you gave me. The userid's on TUE are 2 and 3,
not 4 and 8. But beyond that, you've changed the requirements. Let's see.
You want the times of the overlaps ....

Hmmm, this almost gets us there:
SELECT t.userID, t.day,
min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
FROM TimeblocksPerPerson AS t INNER JOIN
MultiReservedTimeblocks AS m
ON (t.day = m.day) AND (t.timeblock = m.timeblock)
group by t.userID, t.day
ORDER BY t.day,min(t.timeblock)

I think this will work:

create a saved query called MaxOverlapPerDay with this sql:
SELECT m.day, Max(m.CountOfID) AS MaxOfCountOfID
FROM MultiReservedTimeblocks AS m
GROUP BY m.day

Then this sql gives you the following resultset:
SELECT t.userID, t.day,
min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
FROM (TimeblocksPerPerson AS t INNER JOIN
MultiReservedTimeblocks AS m
ON (t.day = m.day) AND (t.timeblock = m.timeblock))
inner join MaxOverlapPerDay AS o
ON m.Day=o.day and m.CountOfID = o.MaxOfCountOfID
group by t.userID, t.day
ORDER BY t.day,min(t.timeblock)

Query7 userID day Expr1002 Expr1003
2 MON 12:30:00 PM 1:00:00 PM
1 MON 12:30:00 PM 1:00:00 PM
3 TUE 12:15:00 PM 1:00:00 PM
2 TUE 12:15:00 PM 1:00:00 PM
5 WED 5:00:00 PM 6:30:00 PM
4 WED 5:00:00 PM 6:30:00 PM
2 WED 5:00:00 PM 6:30:00 PM
1 WED 5:00:00 PM 6:30:00 PM

Note that the Wed common blocks start at 5PM, not 4PM

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 31.10.2005 21:55:50 von reb01501

Bob Barrows [MVP] wrote:
> mike wrote:
>> Hi Bob,
>>
>> Here's what I get when I put this:
>> SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
>> FROM TimeblocksPerPerson AS t INNER JOIN
>> MultiReservedTimeblocks AS m
>> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
>> ORDER BY t.day, t.timeStart
>>
>> in a stored procedure in access and run it:
>>
>> userID day timeStart timeEnd
>> 1 MON 12:00:00 PM 1:00:00 PM
>> 2 MON 12:30:00 PM 1:30:00 PM
>> 2 TUE 12:00:00 PM 2:00:00 PM
>> 3 TUE 12:15:00 PM 1:00:00 PM
>> 2 WED 1:00:00 PM 10:00:00 PM
>> 1 WED 3:00:00 PM 6:30:00 PM
>> 5 WED 4:00:00 PM 8:00:00 PM
>> 4 WED 5:00:00 PM 7:00:00 PM
>>
>> Here's what I was hoping to get:
>>
>> day timeStart timeEnd userID
>> MON 12:30 1:00 1
>> MON 12:30 1:00 2
>> TUE 12:15 1:00 4
>> TUE 12:15 1:00 8
>> WED 4:00 6:30 1
>> WED 4:00 6:30 4
>> WED 4:00 6:30 5
>> WED 4:00 6:30 2
>>
>> The big difference is that the second list has the times for the
>> overlap in each record, but for some reason I dont get this result...
>> any ideas what I am doing wrong?
>> Thanks,
>> Mike
>
> I think you are confusing id's. For example, there is no record with
> a user id of 8 in the sample data you gave me. The userid's on TUE
> are 2 and 3, not 4 and 8. But beyond that, you've changed the
> requirements. Let's see. You want the times of the overlaps ....
>
> Hmmm, this almost gets us there:
> SELECT t.userID, t.day,
> min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
> FROM TimeblocksPerPerson AS t INNER JOIN
> MultiReservedTimeblocks AS m
> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
> group by t.userID, t.day
> ORDER BY t.day,min(t.timeblock)
>
>
> I think this will work:
>
> create a saved query called MaxOverlapPerDay with this sql:
> SELECT m.day, Max(m.CountOfID) AS MaxOfCountOfID
> FROM MultiReservedTimeblocks AS m
> GROUP BY m.day
>
> Then this sql gives you the following resultset:
> SELECT t.userID, t.day,
> min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
> FROM (TimeblocksPerPerson AS t INNER JOIN
> MultiReservedTimeblocks AS m
> ON (t.day = m.day) AND (t.timeblock = m.timeblock))
> inner join MaxOverlapPerDay AS o
> ON m.Day=o.day and m.CountOfID = o.MaxOfCountOfID
> group by t.userID, t.day
> ORDER BY t.day,min(t.timeblock)
>
> Query7 userID day Expr1002 Expr1003
> 2 MON 12:30:00 PM 1:00:00 PM
> 1 MON 12:30:00 PM 1:00:00 PM
> 3 TUE 12:15:00 PM 1:00:00 PM
> 2 TUE 12:15:00 PM 1:00:00 PM
> 5 WED 5:00:00 PM 6:30:00 PM
> 4 WED 5:00:00 PM 6:30:00 PM
> 2 WED 5:00:00 PM 6:30:00 PM
> 1 WED 5:00:00 PM 6:30:00 PM
>
>
> Note that the Wed common blocks start at 5PM, not 4PM
>

While this last one works for your particular set of sample data, it fails
if you, for instance, add a couple overlapping appointments on WED AM.

I think the previous query is the closest we can get to your solution with
straight sql. To get the rest of the way, you will need to loop through that
resultset and calculate the overlaps in vbscript code. it might help if you
add

min(countofid),max(countofid)

to the select list in that query. Beyond that, I'm out of time for today.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 01.11.2005 00:57:16 von Chris Hohmann

"Bob Barrows [MVP]" wrote in message
news:ubGo51l3FHA.2144@TK2MSFTNGP09.phx.gbl...
> Bob Barrows [MVP] wrote:
>> mike wrote:
>>> Hi Bob,
>>>
>>> Here's what I get when I put this:
>>> SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
>>> FROM TimeblocksPerPerson AS t INNER JOIN
>>> MultiReservedTimeblocks AS m
>>> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
>>> ORDER BY t.day, t.timeStart
>>>
>>> in a stored procedure in access and run it:
>>>
>>> userID day timeStart timeEnd
>>> 1 MON 12:00:00 PM 1:00:00 PM
>>> 2 MON 12:30:00 PM 1:30:00 PM
>>> 2 TUE 12:00:00 PM 2:00:00 PM
>>> 3 TUE 12:15:00 PM 1:00:00 PM
>>> 2 WED 1:00:00 PM 10:00:00 PM
>>> 1 WED 3:00:00 PM 6:30:00 PM
>>> 5 WED 4:00:00 PM 8:00:00 PM
>>> 4 WED 5:00:00 PM 7:00:00 PM
>>>
>>> Here's what I was hoping to get:
>>>
>>> day timeStart timeEnd userID
>>> MON 12:30 1:00 1
>>> MON 12:30 1:00 2
>>> TUE 12:15 1:00 4
>>> TUE 12:15 1:00 8
>>> WED 4:00 6:30 1
>>> WED 4:00 6:30 4
>>> WED 4:00 6:30 5
>>> WED 4:00 6:30 2
>>>
>>> The big difference is that the second list has the times for the
>>> overlap in each record, but for some reason I dont get this result...
>>> any ideas what I am doing wrong?
>>> Thanks,
>>> Mike
>>
>> I think you are confusing id's. For example, there is no record with
>> a user id of 8 in the sample data you gave me. The userid's on TUE
>> are 2 and 3, not 4 and 8. But beyond that, you've changed the
>> requirements. Let's see. You want the times of the overlaps ....
>>
>> Hmmm, this almost gets us there:
>> SELECT t.userID, t.day,
>> min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
>> FROM TimeblocksPerPerson AS t INNER JOIN
>> MultiReservedTimeblocks AS m
>> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
>> group by t.userID, t.day
>> ORDER BY t.day,min(t.timeblock)
>>
>>
>> I think this will work:
>>
>> create a saved query called MaxOverlapPerDay with this sql:
>> SELECT m.day, Max(m.CountOfID) AS MaxOfCountOfID
>> FROM MultiReservedTimeblocks AS m
>> GROUP BY m.day
>>
>> Then this sql gives you the following resultset:
>> SELECT t.userID, t.day,
>> min(t.timeblock) ,dateadd("n",15,max( t.timeblock))
>> FROM (TimeblocksPerPerson AS t INNER JOIN
>> MultiReservedTimeblocks AS m
>> ON (t.day = m.day) AND (t.timeblock = m.timeblock))
>> inner join MaxOverlapPerDay AS o
>> ON m.Day=o.day and m.CountOfID = o.MaxOfCountOfID
>> group by t.userID, t.day
>> ORDER BY t.day,min(t.timeblock)
>>
>> Query7 userID day Expr1002 Expr1003
>> 2 MON 12:30:00 PM 1:00:00 PM
>> 1 MON 12:30:00 PM 1:00:00 PM
>> 3 TUE 12:15:00 PM 1:00:00 PM
>> 2 TUE 12:15:00 PM 1:00:00 PM
>> 5 WED 5:00:00 PM 6:30:00 PM
>> 4 WED 5:00:00 PM 6:30:00 PM
>> 2 WED 5:00:00 PM 6:30:00 PM
>> 1 WED 5:00:00 PM 6:30:00 PM
>>
>>
>> Note that the Wed common blocks start at 5PM, not 4PM
>>
>
> While this last one works for your particular set of sample data, it fails
> if you, for instance, add a couple overlapping appointments on WED AM.
>
> I think the previous query is the closest we can get to your solution with
> straight sql. To get the rest of the way, you will need to loop through
> that resultset and calculate the overlaps in vbscript code. it might help
> if you add
>
> min(countofid),max(countofid)
>
> to the select list in that query. Beyond that, I'm out of time for today.

Here's a straight sql proof of concept. I think the minimum_attendees
parameter is what Mike was driving at in his original post.

PARAMETERS minimum_attendees Long;
SELECT S.userID, M.*
FROM Schedule AS S INNER JOIN [SELECT
S1.day,
IIF(S1.timeStart > S2.timeStart, S1.timeStart, S2.timeStart) AS timeStart,
IIF(S1.timeEnd < S2.timeEnd, S1.timeEnd, S2.timeEnd) AS timeEnd
FROM
Schedule AS S1
INNER JOIN
Schedule AS S2
ON
S1.userID <> S2.userID AND
S1.day = S2.day AND
S1.timeStart < S2.timeEnd AND
S1.timeEnd > S2.timeStart
GROUP BY
S1.day,
IIF(S1.timeStart > S2.timeStart, S1.timeStart, S2.timeStart),
IIF(S1.timeEnd < S2.timeEnd, S1.timeEnd, S2.timeEnd)
HAVING
COUNT(*) >= minimum_attendees
]. AS M ON (S.day = M.day) AND (S.timeStart <= M.timeStart) AND (S.timeEnd
>= M.timeEnd)
ORDER BY M.day, M.timeStart, M.timeEnd;

Re: how to match time ranges for multiple records in a query?

am 01.11.2005 02:49:41 von Chris Hohmann

"Chris Hohmann" wrote in message
news:OsdSicn3FHA.268@TK2MSFTNGP10.phx.gbl...
> "Bob Barrows [MVP]" wrote in message
> news:ubGo51l3FHA.2144@TK2MSFTNGP09.phx.gbl...

[snip]

>> I think the previous query is the closest we can get to your solution
>> with straight sql. To get the rest of the way, you will need to loop
>> through that resultset and calculate the overlaps in vbscript code. it
>> might help if you add
>>
>> min(countofid),max(countofid)
>>
>> to the select list in that query. Beyond that, I'm out of time for today.
>
>
> Here's a straight sql proof of concept. I think the minimum_attendees
> parameter is what Mike was driving at in his original post.

[snip]

I think I misunderstood the requirements. After re-reading this thread and
examining the desired output, I understand the requirements to be "list the
attendees for the meeting with the highest possible number of attendees for
each day." If that is correct, it can be accomplished with two(2) queries.
The first is the Meetings query which lists the day, start time and end time
for all possible meetings. The second query, the DailyMeetings query,
aggregates the Meetings query to find the meeting(s) each day with the
highest possible number of attendees. The resultant subquery is then joined
with the Schedule table to list the attendees for the meeting(s) with the
highest possible number of attendees for each day. Here are the queries:

---BEGIN Meetings Query---

SELECT M.day, M.timeStart, M.timeEnd, COUNT (*) AS attendees
FROM Schedule AS S
INNER JOIN ( SELECT S1.day,
IIF(S1.timeStart > S2.timeStart, S1.timeStart,
S2.timeStart) AS timeStart,
IIF(S1.timeEnd < S2.timeEnd, S1.timeEnd, S2.timeEnd)
AS timeEnd
FROM Schedule AS S1
INNER JOIN Schedule AS S2
ON S1.userID <> S2.userID AND
S1.day = S2.day AND
S1.timeStart < S2.timeEnd AND
S1.timeEnd > S2.timeStart
GROUP BY S1.day,
IIF(S1.timeStart > S2.timeStart, S1.timeStart,
S2.timeStart),
IIF(S1.timeEnd < S2.timeEnd, S1.timeEnd, S2.timeEnd)
) AS M
ON S.timeEnd >= M.timeEnd AND
S.timeStart <= M.timeStart AND
S.day = M.day
GROUP BY M.day, M.timeStart, M.timeEnd

---END Meetings Query---

---BEGIN DailyMeetings Query---

SELECT S.userID, M.day, M.timeStart, M.timeEnd
FROM Schedule AS S
INNER JOIN (
SELECT M.day, M.timeStart, M.timeEnd
FROM Meetings AS M
INNER JOIN (
SELECT day, MAX(attendees) AS max_attendees
FROM Meetings
GROUP BY day
) AS D
ON M.day = D.day AND
M.attendees = D.max_attendees
) AS M
ON S.day = M.day AND
S.timeStart <= M.timeStart AND
S.timeEnd >= M.timeEnd

---END DailyMeetings Query---

Notes:
1. This could have been accomplished in one(1) query by substituting a
subquery for every instance of the Meetings query into the DailyMeetings
query, but that would have made the resulting query much more difficult to
understand.

2. The above solution does not account for situations where there are
multiple meetings with the maximum attendees per day. In such a case, all
meetings with the maximum attendees per day will be listed. You'll need to
add another level of nesting, perhaps based on earliest start time, to
settle ties.

Re: how to match time ranges for multiple records in a query?

am 01.11.2005 14:58:28 von reb01501

Chris Hohmann wrote:
> [snip]
>
> I think I misunderstood the requirements. After re-reading this
> thread and examining the desired output, I understand the
> requirements to be "list the attendees for the meeting with the
> highest possible number of attendees for each day."

I don't think that is correct, but I will wait for Mike to weigh in.

Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 08.11.2005 21:57:08 von Mike

Sorry for the late reply, for some reason google didn't show this
thread on my recent threads with some activity.

I changed the Schedules table to make this easier by removing the day
column, now it looks like this (real data):

ID userID timeStart timeEnd
87 8 11/7/2005 8:00:00 AM 11/7/2005 11:30:00 PM
88 9 11/7/2005 8:00:00 PM 11/7/2005 11:00:00 PM
89 9 11/8/2005 8:00:00 PM 11/8/2005 11:00:00 PM
90 9 11/9/2005 8:00:00 PM 11/9/2005 11:00:00 PM
91 9 11/10/2005 8:00:00 PM 11/10/2005 11:00:00 PM
92 9 11/11/2005 8:00:00 PM 11/11/2005 11:00:00 PM
93 9 11/12/2005 8:00:00 PM 11/12/2005 11:00:00 PM
94 9 11/13/2005 8:00:00 PM 11/13/2005 11:00:00 PM
95 10 11/7/2005 2:00:00 AM 11/7/2005 4:30:00 AM
96 10 11/8/2005 2:00:00 AM 11/8/2005 4:30:00 AM
97 10 11/9/2005 2:00:00 AM 11/9/2005 4:30:00 AM
98 10 11/10/2005 2:00:00 AM 11/10/2005 4:30:00 AM
99 10 11/11/2005 2:00:00 AM 11/11/2005 5:45:00 AM
101 12 11/11/2005 10:30:00 PM 11/11/2005 4:45:00 AM
102 12 11/12/2005 3:00:00 PM 11/12/2005 9:00:00 PM
103 12 11/12/2005 12:30:00 AM 11/12/2005 4:45:00 AM
104 12 11/13/2005 3:00:00 PM 11/13/2005 10:00:00 PM
105 13 11/8/2005 12:00:00 PM 11/8/2005 5:30:00 PM
106 14 11/7/2005 5:00:00 AM 11/7/2005 9:00:00 PM
107 14 11/8/2005 5:00:00 AM 11/8/2005 4:45:00 AM
108 14 11/9/2005 5:00:00 AM 11/9/2005 4:45:00 AM
109 14 11/10/2005 5:00:00 AM 11/10/2005 4:45:00 AM
110 14 11/11/2005 5:00:00 AM 11/11/2005 4:45:00 AM
111 14 11/12/2005 5:00:00 AM 11/12/2005 9:00:00 PM
131 17 11/7/2005 5:00:00 AM 11/8/2005 5:00:00 AM
132 17 11/8/2005 5:00:00 AM 11/9/2005 4:30:00 AM
133 17 11/9/2005 5:00:00 AM 11/10/2005 4:00:00 AM
134 17 11/10/2005 5:00:00 AM 11/10/2005 5:30:00 AM
135 17 11/12/2005 5:00:00 AM 11/12/2005 5:30:00 PM
136 17 11/13/2005 2:30:00 PM 11/14/2005 5:00:00 AM
137 7 11/8/2005 2:00:00 AM 11/8/2005 7:30:00 AM
143 21 11/7/2005 11:00:00 PM 11/7/2005 11:30:00 PM
146 25 11/8/2005 5:30:00 AM 11/8/2005 8:30:00 AM
147 25 11/9/2005 5:30:00 AM 11/9/2005 8:30:00 AM
148 25 11/10/2005 5:30:00 AM 11/10/2005 8:30:00 AM
149 25 11/11/2005 5:30:00 AM 11/11/2005 8:30:00 AM
150 25 11/12/2005 5:30:00 AM 11/12/2005 8:30:00 AM
152 25 11/13/2005 2:30:00 AM 11/13/2005 8:30:00 AM
153 25 11/14/2005 2:30:00 AM 11/14/2005 8:30:00 AM
154 26 11/8/2005 4:30:00 AM 11/8/2005 6:00:00 AM
155 26 11/9/2005 4:00:00 AM 11/9/2005 6:00:00 AM
156 26 11/10/2005 4:30:00 AM 11/10/2005 6:00:00 AM
157 26 11/11/2005 4:30:00 AM 11/11/2005 6:00:00 AM
158 26 11/12/2005 4:00:00 AM 11/12/2005 6:00:00 AM
159 26 11/12/2005 3:00:00 PM 11/13/2005 6:00:00 AM
160 26 11/14/2005 4:00:00 AM 11/14/2005 5:00:00 AM

What I want to do is find ALL of the overlapping times, the time range
of the overlaps, and which userids are part of each overlap.

i.e., lets say user1 is available from 2-6 pm on 11/7/05 and user2 is
available from 3-7 pm on 11/7/05. Then the overlap would be from 3-6
pm.

I hope this is more clear and understandable.. Is it easier now that I
rolled the day into the time columns?

-Mike

Re: how to match time ranges for multiple records in a query?

am 08.11.2005 22:15:49 von reb01501

mike wrote:
> Sorry for the late reply, for some reason google didn't show this
> thread on my recent threads with some activity.
>
> I changed the Schedules table to make this easier by removing the day
> column, now it looks like this (real data):
>
>
> What I want to do is find ALL of the overlapping times, the time range
> of the overlaps, and which userids are part of each overlap.
>
> i.e., lets say user1 is available from 2-6 pm on 11/7/05 and user2 is
> available from 3-7 pm on 11/7/05. Then the overlap would be from 3-6
> pm.
>
> I hope this is more clear and understandable.. Is it easier now that
> I rolled the day into the time columns?
>
No, that wasn't the problem.

Let's clarify the specs. Show us the entire recordset (field names and data)
you wish to create from that sample data.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 08.11.2005 22:35:27 von McKirahan

"mike" wrote in message
news:1131483428.302442.246690@f14g2000cwb.googlegroups.com.. .
> Sorry for the late reply, for some reason google didn't show this
> thread on my recent threads with some activity.
>
> I changed the Schedules table to make this easier by removing the day
> column, now it looks like this (real data):
>
>
> ID userID timeStart timeEnd
> 87 8 11/7/2005 8:00:00 AM 11/7/2005 11:30:00 PM
> 88 9 11/7/2005 8:00:00 PM 11/7/2005 11:00:00 PM
> 89 9 11/8/2005 8:00:00 PM 11/8/2005 11:00:00 PM
> 90 9 11/9/2005 8:00:00 PM 11/9/2005 11:00:00 PM
> 91 9 11/10/2005 8:00:00 PM 11/10/2005 11:00:00 PM
> 92 9 11/11/2005 8:00:00 PM 11/11/2005 11:00:00 PM
> 93 9 11/12/2005 8:00:00 PM 11/12/2005 11:00:00 PM
> 94 9 11/13/2005 8:00:00 PM 11/13/2005 11:00:00 PM
> 95 10 11/7/2005 2:00:00 AM 11/7/2005 4:30:00 AM
> 96 10 11/8/2005 2:00:00 AM 11/8/2005 4:30:00 AM
> 97 10 11/9/2005 2:00:00 AM 11/9/2005 4:30:00 AM
> 98 10 11/10/2005 2:00:00 AM 11/10/2005 4:30:00 AM
> 99 10 11/11/2005 2:00:00 AM 11/11/2005 5:45:00 AM
> 101 12 11/11/2005 10:30:00 PM 11/11/2005 4:45:00 AM
> 102 12 11/12/2005 3:00:00 PM 11/12/2005 9:00:00 PM
> 103 12 11/12/2005 12:30:00 AM 11/12/2005 4:45:00 AM
> 104 12 11/13/2005 3:00:00 PM 11/13/2005 10:00:00 PM
> 105 13 11/8/2005 12:00:00 PM 11/8/2005 5:30:00 PM
> 106 14 11/7/2005 5:00:00 AM 11/7/2005 9:00:00 PM
> 107 14 11/8/2005 5:00:00 AM 11/8/2005 4:45:00 AM
> 108 14 11/9/2005 5:00:00 AM 11/9/2005 4:45:00 AM
> 109 14 11/10/2005 5:00:00 AM 11/10/2005 4:45:00 AM
> 110 14 11/11/2005 5:00:00 AM 11/11/2005 4:45:00 AM
> 111 14 11/12/2005 5:00:00 AM 11/12/2005 9:00:00 PM
> 131 17 11/7/2005 5:00:00 AM 11/8/2005 5:00:00 AM
> 132 17 11/8/2005 5:00:00 AM 11/9/2005 4:30:00 AM
> 133 17 11/9/2005 5:00:00 AM 11/10/2005 4:00:00 AM
> 134 17 11/10/2005 5:00:00 AM 11/10/2005 5:30:00 AM
> 135 17 11/12/2005 5:00:00 AM 11/12/2005 5:30:00 PM
> 136 17 11/13/2005 2:30:00 PM 11/14/2005 5:00:00 AM
> 137 7 11/8/2005 2:00:00 AM 11/8/2005 7:30:00 AM
> 143 21 11/7/2005 11:00:00 PM 11/7/2005 11:30:00 PM
> 146 25 11/8/2005 5:30:00 AM 11/8/2005 8:30:00 AM
> 147 25 11/9/2005 5:30:00 AM 11/9/2005 8:30:00 AM
> 148 25 11/10/2005 5:30:00 AM 11/10/2005 8:30:00 AM
> 149 25 11/11/2005 5:30:00 AM 11/11/2005 8:30:00 AM
> 150 25 11/12/2005 5:30:00 AM 11/12/2005 8:30:00 AM
> 152 25 11/13/2005 2:30:00 AM 11/13/2005 8:30:00 AM
> 153 25 11/14/2005 2:30:00 AM 11/14/2005 8:30:00 AM
> 154 26 11/8/2005 4:30:00 AM 11/8/2005 6:00:00 AM
> 155 26 11/9/2005 4:00:00 AM 11/9/2005 6:00:00 AM
> 156 26 11/10/2005 4:30:00 AM 11/10/2005 6:00:00 AM
> 157 26 11/11/2005 4:30:00 AM 11/11/2005 6:00:00 AM
> 158 26 11/12/2005 4:00:00 AM 11/12/2005 6:00:00 AM
> 159 26 11/12/2005 3:00:00 PM 11/13/2005 6:00:00 AM
> 160 26 11/14/2005 4:00:00 AM 11/14/2005 5:00:00 AM
>
> What I want to do is find ALL of the overlapping times, the time range
> of the overlaps, and which userids are part of each overlap.
>
> i.e., lets say user1 is available from 2-6 pm on 11/7/05 and user2 is
> available from 3-7 pm on 11/7/05. Then the overlap would be from 3-6
> pm.
>
> I hope this is more clear and understandable.. Is it easier now that I
> rolled the day into the time columns?
>
> -Mike
>

Are the instances of "timeStart" before "timeEnd" valid?

ID userID timeStart timeEnd
101 12 11/11/2005 10:30:00 PM 11/11/2005 4:45:00 AM
107 14 11/8/2005 5:00:00 AM 11/8/2005 4:45:00 AM
108 14 11/9/2005 5:00:00 AM 11/9/2005 4:45:00 AM
109 14 11/10/2005 5:00:00 AM 11/10/2005 4:45:00 AM
110 14 11/11/2005 5:00:00 AM 11/11/2005 4:45:00 AM

Re: how to match time ranges for multiple records in a query?

am 09.11.2005 07:48:26 von Mike

Sorry, my code that filled that database was wrong and shouldnt have
allowed that to happen (no end times before start times allowed)

here's new sample data from the table Schedules

ID userID timeStart timeEnd
1 1 11/7/2005 1:00:00 AM 11/7/2005 4:00:00 AM
2 2 11/7/2005 2:00:00 AM 11/7/2005 7:00:00 AM
3 3 11/7/2005 1:30:00 AM 11/7/2005 3:00:00 AM
4 4 11/7/2005 10:30:00 PM 11/8/2005 2:00:00 AM
5 5 11/7/2005 9:00:00 PM 11/8/2005 1:00:00 AM
6 6 11/8/2005 2:00:00 PM 11/8/2005 3:00:00 PM
7 1 11/7/2005 10:00:00 PM 11/8/2005 1:00:00 AM

and the result I am looking for:
ID userID timeStart timeEnd
1 1 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
2 2 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
3 3 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
4 1 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
5 4 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
6 5 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM

notice that the overlap for each set of overlapping times is listed in
all the start/end time results to make it easy to grab. Also note
userID 6 didnt match any of the times and is only a single person, so
there is no meeting for him.

Re: how to match time ranges for multiple records in a query?

am 09.11.2005 19:34:08 von Chris Hohmann

Re: how to match time ranges for multiple records in a query?

am 10.11.2005 02:24:01 von Mike

Hi Chris,

I put the query in Access 2000 Query SQL design view and tried it
this is what I get:
userID timeStart timeEnd
2 11/7/2005 2:00:00 AM 11/7/2005 4:00:00 AM
3 11/7/2005 1:30:00 AM 11/7/2005 3:00:00 AM
1 11/7/2005 2:00:00 AM 11/7/2005 4:00:00 AM
3 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
1 11/7/2005 1:30:00 AM 11/7/2005 3:00:00 AM
2 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
5 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
1 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
4 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
1 11/7/2005 10:00:00 PM 11/8/2005 1:00:00 AM
4 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
5 11/7/2005 10:00:00 PM 11/8/2005 1:00:00 AM

Re: how to match time ranges for multiple records in a query?

am 10.11.2005 09:06:56 von Mike

Bob, could you tell me how to adapt this code for the new table format,
without the day of the week? I am thinking that the problem with not
giving the overlap can be solved in vbscript, and its the best solution
yet....
The good news is that I have come up with a solution. The first step is
to
create a table called TimeBlocks consisting of a single date/Time
column
called TimeBlock. Put 96 records in this table: #12:00 AM#, #12:15 AM#,
....,
#11:45 PM#

Now, create a saved query called "TimeblocksPerPerson" using this sql
(I
called the table containing your sample data "Schedules" - substitute
your
table's name):

SELECT s.*, timeblock
FROM TimeBlocks AS b INNER JOIN Schedules AS s
ON (b.timeblock>=dateadd("s",-1,s.timeStart))
AND (b.timeblock ORDER BY [day], ID, timeblock

In SQL Server, you would create a similarly-named view with this sql
(in
T-SQL, you will need to get rid of the quotes in the dateadd
arguments).

Next create another saved query called "MultiReservedTimeblocks" using
this
sql:

SELECT t.day, t.timeblock, Count(t.ID) AS CountOfID
FROM TimeblocksPerPerson AS t
GROUP BY t.day, t.timeblock
HAVING Count(t.ID)>1

Again, in SQL Server, you would create a similarly-named view with that
sql.

Now, use this sql statement to retrieve your desired resultset:

SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
FROM TimeblocksPerPerson AS t INNER JOIN
MultiReservedTimeblocks AS m
ON (t.day = m.day) AND (t.timeblock = m.timeblock)
ORDER BY t.day, t.timeStart

HTH,
Bob Barrows

Re: how to match time ranges for multiple records in a query?

am 10.11.2005 22:16:12 von reb01501

I will try to get to this tonight - no guarantees. :-)
mike wrote:
> Bob, could you tell me how to adapt this code for the new table
> format, without the day of the week? I am thinking that the problem
> with not giving the overlap can be solved in vbscript, and its the
> best solution yet....
> The good news is that I have come up with a solution. The first step
> is to
> create a table called TimeBlocks consisting of a single date/Time
> column
> called TimeBlock. Put 96 records in this table: #12:00 AM#, #12:15
> AM#, ...,
> #11:45 PM#
>
> Now, create a saved query called "TimeblocksPerPerson" using this sql
> (I
> called the table containing your sample data "Schedules" - substitute
> your
> table's name):
>
>
> SELECT s.*, timeblock
> FROM TimeBlocks AS b INNER JOIN Schedules AS s
> ON (b.timeblock>=dateadd("s",-1,s.timeStart))
> AND (b.timeblock > ORDER BY [day], ID, timeblock
>
>
> In SQL Server, you would create a similarly-named view with this sql
> (in
> T-SQL, you will need to get rid of the quotes in the dateadd
> arguments).
>
>
> Next create another saved query called "MultiReservedTimeblocks" using
> this
> sql:
>
>
> SELECT t.day, t.timeblock, Count(t.ID) AS CountOfID
> FROM TimeblocksPerPerson AS t
> GROUP BY t.day, t.timeblock
> HAVING Count(t.ID)>1
>
>
> Again, in SQL Server, you would create a similarly-named view with
> that sql.
>
>
> Now, use this sql statement to retrieve your desired resultset:
>
>
> SELECT DISTINCT t.userID, t.day, t.timeStart, t.timeEnd
> FROM TimeblocksPerPerson AS t INNER JOIN
> MultiReservedTimeblocks AS m
> ON (t.day = m.day) AND (t.timeblock = m.timeblock)
> ORDER BY t.day, t.timeStart
>
>
> HTH,
> Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to match time ranges for multiple records in a query?

am 11.11.2005 21:28:19 von reb01501

mike wrote:
> Sorry, my code that filled that database was wrong and shouldnt have
> allowed that to happen (no end times before start times allowed)
>
> here's new sample data from the table Schedules
>
> ID userID timeStart timeEnd
> 1 1 11/7/2005 1:00:00 AM 11/7/2005 4:00:00 AM
> 2 2 11/7/2005 2:00:00 AM 11/7/2005 7:00:00 AM
> 3 3 11/7/2005 1:30:00 AM 11/7/2005 3:00:00 AM
> 4 4 11/7/2005 10:30:00 PM 11/8/2005 2:00:00 AM
> 5 5 11/7/2005 9:00:00 PM 11/8/2005 1:00:00 AM
> 6 6 11/8/2005 2:00:00 PM 11/8/2005 3:00:00 PM
> 7 1 11/7/2005 10:00:00 PM 11/8/2005 1:00:00 AM
>
> and the result I am looking for:
> ID userID timeStart timeEnd
> 1 1 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
> 2 2 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
> 3 3 11/7/2005 2:00:00 AM 11/7/2005 3:00:00 AM
> 4 1 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
> 5 4 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
> 6 5 11/7/2005 10:30:00 PM 11/8/2005 1:00:00 AM
>
> notice that the overlap for each set of overlapping times is listed in
> all the start/end time results to make it easy to grab. Also note
> userID 6 didnt match any of the times and is only a single person, so
> there is no meeting for him.

This set of data has schedules that span two days, which is different from
your original set of data. Was this intentional? Will you really have
schedules that span multiple days? If so, my original solution will not
work. let me know.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to match time ranges for multiple records in a query?

am 11.11.2005 22:24:34 von Mike

> This set of data has schedules that span two days, which is different from
> your original set of data. Was this intentional? Will you really have
> schedules that span multiple days? If so, my original solution will not
> work. let me know.

Yes, I ran into the problem that people wanted to put times like 10 pm
to 2 am, which would result in the end time being less than the start
time. So what I did was allow the user to say 10 pm on 11/7 to 2 am on
11/8, thereby making it clear that the end time was not less than the
start time. Is there another way to do it now??