Solved

Help Needed With Query

Posted on 2015-01-17
5
134 Views
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)
0
Comment
Question by:penlandt
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:penlandt
Comment Utility
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.

Query1

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;

Query2

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]));

Query3

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]));
0
 
LVL 1

Author Comment

by:penlandt
Comment Utility
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.

Query1

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;

Query2

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));
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
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
where
t1.participantid=@participant1 and t2.participantid=@participant2 or
t2.participantid=@participant1 and t1.participantid=@participant2
) a
group by teamno,p1,p2
0
 
LVL 1

Author Closing Comment

by:penlandt
Comment Utility
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!

CREATE PROCEDURE procName
      @StartDate datetime,
      @ParticipantA int,
      @ParticipantB int
AS
BEGIN
      SET NOCOUNT ON;

CREATE PROCEDURE procName
      @StartDate datetime,
      @ParticipantA int,
      @ParticipantB int
AS
BEGIN
      SET NOCOUNT ON;

SELECT TOP 1 DATEDIFF(d,MAX(IntervalStartDate),@StartDate),TeamNumber,p1,p2
FROM (
      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
      WHERE
      t1.Participant=@ParticipantA AND t2.Participant=@ParticipantB OR
      t2.Participant=@ParticipantA AND t1.Participant=@ParticipantB
      ) a
GROUP BY TeamNumber,p1,p2
END
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
>  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
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now