[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql to show scheduling conflicts

Posted on 2011-09-06
9
Medium Priority
?
266 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 800 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 1200 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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