Solved

Help Needed With Query

Posted on 2015-01-17
5
147 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
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.

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
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.

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
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
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
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!

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
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard 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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 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