I am trying to come up with a SQL query that will read timestamps from a table cross-referenced with the groups and individuals. What I want is results for each person on any given day with the first and last (min/max) EventTime. With the tables below, for example, an entry for Joe on 7/12/07 would need to be the first and last event on 7/12/07 within either of groups 1001 and 1003. Hopefully this makes sense.
If this is possible, and anyone could help out, I would greatly appreciate it!!!
Here's what the Events table looks like:
Id(PK) | EventTime | GroupId
===================================
1 | 1/22/2007 12:30pm | 1001
2 | 1/22/2007 2:30pm | 1001
3 | 1/23/2007 11:00am | 1002
4 | 1/23/2007 12:30pm | 1002
5 | 1/24/2007 11:00am | 1003
6 | 1/24/2007 12:30pm | 1003
7 | 1/24/2007 2:00pm | 1003
Here's what the Groups table looks like:
Id(PK) | Name
================
1001 | Accounting
1002 | HR
1003 | Sales
1004 | Marketing
Here's what the Individuals table looks like:
Id(PK) | Name | Primary | Secondary
===============================
2001 | Joe | 1003 | 1001
2002 | Bill | 1002 | 1003
2003 | Sam | 1001 | 1002
2004 | Jack | 1002 | 1003
What I need is output that would look something like this:
Date | StartTime | EndTime | Person
==================================
1/22/07 | 12:30pm | 2:30pm | Joe
1/22/07 | 12:30pm | 2:30pm | Sam
1/23/07 | 11:00am | 12:30pm | Bill
1/23/07 | 11:00am | 12:30pm | Jack
1/24/07 | 11:00am | 2:00pm | Joe
1/24/07 | 11:00am | 2:00pm | Bill
1/24/07 | 11:00am | 2:00pm | Jack