Sql to show scheduling conflicts


Need some help coming up with a query to show scheduling conflicts for referees.
Basically I need to show games for referees in which the games overlap.
The tables give us the game start times & game length in minutes.
I've simplified the table structure in the attached mdb file

The query below shows the relationship between the tables.

SELECT Persons.PersonId, Persons.NameLast, Persons.NameFirst, Games.GameId, Games.GameNo, Games.GameStartDt, Divisions.GameLength, OfficialPositions.OfficialPositionId, OfficialPositions.PositionName, Divisions.DivisionId, Divisions.DivisionName
FROM Persons INNER JOIN ((Divisions INNER JOIN OfficialPositions ON Divisions.DivisionId = OfficialPositions.DivisionId) INNER JOIN (Games INNER JOIN Games2Persons ON Games.GameId = Games2Persons.GameId) ON OfficialPositions.OfficialPositionId = Games2Persons.OfficialPositionId) ON Persons.PersonId = Games2Persons.PersonId
ORDER BY Persons.PersonId, Games.GameStartDt;

Query results
PersonId      NameLast      NameFirst      GameId      GameNo      GameStartDt      GameLength      OfficialPositionId      PositionName      DivisionId      DivisionName
1      RefA      NameA      1      1*1*1      7/9/11 12:00 PM      60      1      Center1      1      U10
1      RefA      NameA      6      1*1*6      7/9/11 2:20 PM        70      3      Center1      2      U12
1      RefA      NameA      9      1*1*9      7/9/11 4:00 PM              60      1      Center1      1      U10
1      RefA      NameA      10      1*1*10      7/9/11 4:40 PM              70      3      Center1      2      U12
2      RefB      NameB      2      1*1*2      7/9/11 12:00 PM      70      3      Center1      2      U12
2      RefB      NameB      3      1*1*3      7/9/11 1:00 PM         60      1      Center1      1      U10
2      RefB      NameB      8      1*1*8      7/9/11 3:30 PM        70      3      Center1      2      U12
50      RefC      NameC      4      1*1*4      7/9/11 1:10 PM        70      3      Center1      2      U12
50      RefC      NameC      5      1*1*5      7/9/11 2:00 PM        60      1      Center1      1      U10
50      RefC      NameC      7      1*1*7      7/9/11 3:00 PM             60      1      Center1      1      U10

I need a query to return these conflicts:
RefA conflicts:
Gameid      GameNo      GameStartDt      GameLength
9              1*1*9      7/9/11 4:00 PM          60
10             1*1*10      7/9/11 4:40 PM          70

RefB conflicts:
Gameid      GameNo      GameStartDt      GameLength
2      1*1*2      7/9/11 12:00 PM      70
3      1*1*3      7/9/11 1:00 PM       60

RefC conflicts:
Gameid      GameNo      GameStartDt      GameLength
4              1*1*4      7/9/11 1:10 PM        70
5               1*1*5      7/9/11 2:00 PM        60
E43509-497508.flv
RefAssignGamesBlockageSample.mdb
E43509Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ThomasianConnect With a Mentor Commented:
Sorry for not being clear. The "[RefId]" is used to filter the results for each PersonId.
i.e. Replace "[RefId]" by 1 to show all conficts for PersonId 1.
If you want to show conflicts of all PersonIds, you can simply remove that condition.
SELECT Gameid, GameNo, GameStartDt, GameLength, DATEADD("n",GameLength,GameStartDt)
FROM qryG2P T
WHERE EXISTS (SELECT 1
                  FROM qryG2P
                  WHERE T.PersonId=PersonId
                        AND T.GameId<>GameId
                        AND ((T.GameStartDt > GameStartDt AND T.GameStartDt < DATEADD("n",GameLength,GameStartDt))
                              OR (GameStartDt > T.GameStartDt AND GameStartDt < DATEADD("n",T.GameLength,T.GameStartDt))
                            )
                 )

Open in new window

>>I think this does the trick  ...
You could include the criteria "And CurrentGame.GameId <> NextGame.GameId" to exclude the records that have the same gameid.
You could also change the BETWEEN condition to a combination of > and < operators to exclude GameNo 7 from the conflict list.
But with this query, you could have duplicate results if more that 2 games have conflicting schedules.
SELECT CurrentGame.PersonId, CurrentGame.GameId, CurrentGame.GameStartDt, CurrentGame.GameEndDt, NextGame.GameId, NextGame.GameStartDt, NextGame.GameEndDt
FROM qryG2P AS CurrentGame INNER JOIN qryG2P AS NextGame ON CurrentGame.PersonId = NextGame.PersonId
	And CurrentGame.GameId <> NextGame.GameId
WHERE ((NextGame.GameStartDt > [CurrentGame].[GameStartDt] And NextGame.GameStartDt < [CurrentGame].[GameEndDt]))
ORDER BY CurrentGame.PersonId, CurrentGame.GameStartDt;

Open in new window

0
 
ThomasianCommented:

SELECT Gameid, GameNo, GameStartDt, GameLength, DATEADD("n",GameLength,GameStartDt)
FROM qryG2P T
WHERE PersonId=[RefId]
      AND EXISTS (SELECT 1
                  FROM qryG2P
                  WHERE T.PersonId=PersonId
                        AND T.GameId<>GameId
                        AND ((T.GameStartDt > GameStartDt AND T.GameStartDt < DATEADD("n",GameLength,GameStartDt))
                              OR (GameStartDt > T.GameStartDt AND GameStartDt < DATEADD("n",T.GameLength,T.GameStartDt))
                            )
                 )

Open in new window

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this


SELECT Q1.PersonId, Q1.Gameid, Q1.GameNo, Q1.GameStartDt, Q1.GameLength, (select max(DateAdd("n",[GameLength],[GameStartDt])) From qryG2P where PersonID=Q1.PersonID and GameID<Q1.GameID and  GameStartDt < Q1.GameStartDt) AS NextSchedule
FROM qryG2P AS Q1
WHERE (((Exists (SELECT 1
                  FROM qryG2P
                  WHERE Q1.PersonId=PersonId
                        AND Q1.GameId<>GameId
                        AND ((Q1.GameStartDt > GameStartDt AND Q1.GameStartDt < DATEADD("n",GameLength,GameStartDt))
                              OR (GameStartDt > Q1.GameStartDt AND GameStartDt < DATEADD("n",Q1.GameLength,Q1.GameStartDt))
                            )
                 ))<>False))
ORDER BY Q1.PersonId, Q1.GameStartDt;
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
E43509Author Commented:
I see where you are going however, the query gives me an error "  The expression is typed incorrectly or its too complex to be evaluated.  
Also I don't want to provide the RefID as an input, removing that portion "PersonId=[RefId]' gives me a new error "Data type mismatch in criteria expression"
Thanks for the idea
Thoughts?
0
 
E43509Author Commented:
I think this does the trick  ...
SELECT CurrentGame.PersonId, CurrentGame.GameId, CurrentGame.GameStartDt, CurrentGame.GameEndDt, NextGame.GameId, NextGame.GameStartDt, NextGame.GameEndDt
FROM qryG2P AS CurrentGame INNER JOIN qryG2P AS NextGame ON CurrentGame.PersonId = NextGame.PersonId
WHERE (((NextGame.GameStartDt) Between [CurrentGame].[GameStartDt] And [CurrentGame].[GameEndDt]))
ORDER BY CurrentGame.PersonId, CurrentGame.GameStartDt;

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
E43509

did you try the query at http:#a36493302 ?
0
 
E43509Author Commented:
To capricorn1,  Unless I'm doing something wacko, the query cut and pasted gives me an error.  Data type mismatch in criteria expression.
0
 
E43509Author Commented:
Somehow my local copy of the mdb file got corrupt which is why your queries started erroring out.  I think it is because I had it on a flash drive and may have pulled it out 'unsafely'.
Both capricorn1 and Thomasian queries now run.  
Sorry about that ...

0
 
E43509Author Commented:
Thanks to both capricorn1 and Thomasian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.