Solved

Sql to show scheduling conflicts

Posted on 2011-09-06
9
242 Views
Last Modified: 2012-08-13

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
0
Comment
Question by:E43509
  • 5
  • 2
  • 2
9 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 36493175

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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
ID: 36493302
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
 

Author Comment

by:E43509
ID: 36493306
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
 

Author Comment

by:E43509
ID: 36493320
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36493370
E43509

did you try the query at http:#a36493302 ?
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 300 total points
ID: 36494057
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
 

Author Comment

by:E43509
ID: 36494347
To capricorn1,  Unless I'm doing something wacko, the query cut and pasted gives me an error.  Data type mismatch in criteria expression.
0
 

Author Comment

by:E43509
ID: 36494658
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
 

Author Closing Comment

by:E43509
ID: 36494678
Thanks to both capricorn1 and Thomasian.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now