Solved

Sql to show scheduling conflicts

Posted on 2011-09-06
9
249 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Expert Comment

by:Rey Obrero (Capricorn1)
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Labels prompt to print 4 30
Top 1 of each supplier 55 55
Question about Relationship and Userform in Access Database 17 51
SQL Insert parts by customer 12 31
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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

808 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