Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Help Needed With Query

Posted on 2015-01-17
Medium Priority
Last Modified: 2015-01-18
Hi there.  I'm having a hard time figuring out how to write this query and was wondering if anyone here could help.  Have a look at the table (very simplified) attached.  What I need is a query with three parameters:

@ScheduleDate datetime,
@ParticipantA int,
@ParticipantB int

Here's the hard part:

Using these variables, I need to return an integer representing the difference (in days) between @ScheduleDate and the date that @ParticipantA and @ParticipantB were last assigned to the same Team.  If they have never been assigned to the same team, the query should return 0.

Any questions?  Please let me know.  Thank you very much in advance for any help you can offer.

P.S.  By the way, I am aware that - in the sample data - none of the participants have every been assigned to the same team.  I was trying to keep the data as simple as possible.  In this case, the query I'm looking for would return zero for every pair of participants submitted to it.

Table of Previous Team Assignments (sample)
Question by:penlandt
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
  • 3
  • 2

Author Comment

ID: 40555913
UPDATE:  I've figured out how to do this in three steps but if anyone knows of a more elegant way to accomplish the same thing I'd sure like to know what it is.  Note:  I did this in Access for the sake of speed and simplicity but it can easily be translated into TSQL.


Find every distinct date, team, and participant combination in the table.

SELECT Table1.ScheduleDate, Table1.Team, Table1.ParticipantID, Table1.Participant
FROM Table1
ORDER BY Table1.ParticipantID, Table1.ScheduleDate;


Compare two copies of Query 1 (joined on the date and the team number fields, and find every instance where the participant in the first copy of the table is different than the participant in the second copy.  I suspect by the way - though I could be mistaken - that this could actually be done without the first query, just by using two copies of the table instead,  I'm just not sure.)

SELECT Query1.ScheduleDate, Query1.Team, Query1.Participant, Query1_1.Participant
FROM Query1 INNER JOIN Query1 AS Query1_1 ON (Query1.Team = Query1_1.Team) AND (Query1.ScheduleDate = Query1_1.ScheduleDate)
WHERE (((Query1.Participant)<>[Query1_1].[Participant]));


Finally, subtract the new schedule date from the maximum of the date on which any combination of two players were last scheduled together.  This gives me the integer value I was looking for.

SELECT Nz(CDate([STARTDATE])-Max([ScheduleDate]),0) AS Expr1
FROM Query2
WHERE (((Query2.Query1.Participant)=[PARTICIPANT_A] Or (Query2.Query1.Participant)=[PARTICIPANT_B]) AND ((Query2.Query1_1.Participant)=[PARTICIPANT_A] Or (Query2.Query1_1.Participant)=[PARTICIPANT_B]));

Author Comment

ID: 40555966
UPDATE2:  OK, I was right, this can be done in two queries rather than three.  Still, if it's possible to boil this down to one that would be preferable (I think).  Feel free to tell me why it isn't, if it's not.  BTW...I've translated the Access SQL into TSQL now.


SELECT Table1.ScheduleDate, Table1.Team, Table1.Participant, Table2.Participant
FROM Table1 INNER JOIN Table1 AS Table2 ON (Table1.ScheduleDate = Table2.ScheduleDate) AND (Table1.Team = Table2.Team)
WHERE (((Table1.Participant)<>[Table2].[Participant]))
ORDER BY Table1.ScheduleDate, Table1.Team, Table1.Participant;


SELECT ISNULL(@StartDate-Max([ScheduleDate]),0) AS LastTeamed
FROM Query1
WHERE (((Query1.Table1.Participant)=@ParticipantA Or (Query1.Table1.Participant)=@ParticipantB) AND ((Query1.Table2.Participant)=@ParticipantA Or (Query1.Table2.Participant)=@ParticipantB));
LVL 40

Accepted Solution

Vadim Rapp earned 2000 total points
ID: 40556354
select datediff(d,max(date),@scheduledate),teamno,p1,p2
from (
select t1.date,t1.teamno,t1.participantid p1,t2.participantid p2
from mytable t1 join mytable t2
on t1.date=t2.date and t1.teamno=t2.teamno and t1.participantid<t2.participantid
t1.participantid=@participant1 and t2.participantid=@participant2 or
t2.participantid=@participant1 and t1.participantid=@participant2
) a
group by teamno,p1,p2

Author Closing Comment

ID: 40556432
Thank you so much, you've saved me a lot of work trying to find a way around this.  The query ended up producing two rows with identical data (except the participant names were reversed in the second row) but that was easy enough to fix by only returning one row.  Here's the  stored procedure I'm using now, tested, and doing exactly what I wanted.  Thanks again!

      @StartDate datetime,
      @ParticipantA int,
      @ParticipantB int

      @StartDate datetime,
      @ParticipantA int,
      @ParticipantB int

SELECT TOP 1 DATEDIFF(d,MAX(IntervalStartDate),@StartDate),TeamNumber,p1,p2
      SELECT t1.IntervalStartDate,t1.TeamNumber,t1.Participant p1,t2.Participant p2
      FROM Schedule t1 JOIN Schedule t2
      ON t1.IntervalStartDate=t2.IntervalStartDate AND t1.TeamNumber=t2.TeamNumber AND t1.Participant<>t2.Participant
      t1.Participant=@ParticipantA AND t2.Participant=@ParticipantB OR
      t2.Participant=@ParticipantA AND t1.Participant=@ParticipantB
      ) a
GROUP BY TeamNumber,p1,p2
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40556467
>  The query ended up producing two rows with identical data  (except the participant names were reversed in the second row)

Because you probably did not notice that I changed t1.Participant<>t2.Participant to t1.Participant<t2.Participant

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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