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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 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