Help Needed With Query

Posted on 2015-01-17
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 500 total points
ID: 40556354
select datediff(d,max(date),@scheduledate),teamno,p1,p2
from (
select,t1.teamno,t1.participantid p1,t2.participantid p2
from mytable t1 join mytable t2
on 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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