[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Sql to show scheduling conflicts

Posted on 2011-09-06
9
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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