A Tough Query

1. Senior Member
Join Date
Dec 1969
Posts
6,476

## A Tough Query

At least for me, can&#039;t get it.<BR><BR>Ill explain best I can.<BR><BR>For each Tourney number, there are 4 rounds<BR>10, 1<BR>10, 2<BR>10, 3<BR>10, 4<BR>11, 1<BR>11, 2<BR>11, 3<BR>11, 4<BR><BR>Each has a startDate and an EndDate (Round lasts 2 weeks)<BR><BR>This gets be the current one in progress<BR>SELECT TOP 1 TourID, Tourney, RRnd, startDate, endDate FROM Tourneys WHERE (((TourID)=&#039;"&strTour&"&#039;) AND ((startDate)&#060;Now())) ORDER BY startDate DESC<BR><BR>Would return<BR>10, 3, 02/11/2003, 16/11/2003<BR><BR>Ok, Now to get "All the rounds" of the tourney<BR><BR>SELECT TourID, Tourney, RRnd, startDate, endDate FROM Tourneys WHERE (((TourID)=&#039;"&strTour&"&#039;) AND ((Tourney)="&intTourney&")) ORDER BY Tourney DESC, RRnd DESC<BR><BR><BR>Is there any way to make this a single query to get all the 4 rounds of the current tourney<BR><BR>I still need to know the "Current Round"<BR>which is no probs...<BR>I can compare the dates as i loop through the 4 records...<BR><BR>Make sense?<BR>

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: A Tough Query

SELECT TourID, Tourney, RRnd, startDate, endDate FROM Tourneys WHERE (TourID)=&#039;"&strTour&"&#039; ORDER BY Tourney DESC, RRnd DESC<BR><BR>from what I can see<BR>

3. Senior Member
Join Date
Dec 1969
Posts
6,476

## RE: A Tough Query

That will select them all.<BR>Sorry, reading back I guess i didnt explain properly, sorry about that.<BR><BR>I need to return them according to the start & end dates in progress<BR><BR>So based on todays date<BR>this record is current round<BR>10, 3, 02/11/2003, 16/11/2003<BR><BR>Current Tourney is #10...<BR>I need to return all 4 rounds, of tourney #10<BR>10, 1, dates etc<BR>10, 2, dates etc<BR>10, 3, 02/11/2003, 16/11/2003 <BR>10, 4, 16/11/2003, 30/11/2003<BR><BR>But not the 11&#039;s <BR>11, 1, 30/11/2003, 13/12/2003<BR>11, 2 etc<BR>11, 3 etc<BR>11, 4 etc<BR><BR>Make it a bit clearer?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Just use one query in the other...

Starting from here:<BR><BR>SELECT TOP 1 TourID, Tourney, RRnd, startDate, endDate <BR>FROM Tourneys <BR>WHERE TourID=&#039;###&#039; <BR>AND startDate &#060; Now() ORDER BY startDate DESC <BR><BR>Why not toss out the extraneous stuff to get just the tourney number?<BR><BR>SELECT TOP 1 Tourney<BR>FROM Tourneys <BR>WHERE TourID=&#039;###&#039; <BR>AND startDate &#060; Now() ORDER BY startDate DESC <BR><BR>And then use *THAT* as the inner select here:<BR><BR>SELECT * FROM Tourneys<BR>WHERE Tourney = (<BR> SELECT TOP 1 Tourney<BR> FROM Tourneys <BR> WHERE TourID=&#039;###&#039; <BR> AND startDate &#060; Now() ORDER BY startDate DESC <BR> )<BR>ORDER BY round<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: A Tough Query

[code language="T-SQL"]<BR>SELECT TourID, Tourney, RRnd, startDate, endDate FROM Tourneys WHERE (TourID)=&#039;"&strTour&"&#039; <BR>AND startdate &#060; date() <BR>and end date &#062; date()<BR><BR>ORDER BY startdate, Tourney DESC, RRnd DESC <BR><BR>&#039;is that it?<BR>[/code]

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Nope...

Only *ONE* RRND will be occurring such that its startdate/endate surrouds today.<BR><BR>He wants *ALL* the RRNDs that match the TOURNEY value of that RRND that your query would get.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
6,476

## Perfect

SELECT * FROM Tourneys <BR>WHERE Tourney = ( <BR> SELECT TOP 1 Tourney <BR> FROM Tourneys <BR> WHERE TourID=&#039;###&#039; <BR> AND startDate &#060; Now() ORDER BY startDate DESC <BR> ) <BR>ORDER BY round <BR><BR>Exactly what I needed.<BR>Just had no idea how to put it together....<BR><BR>As WK mentioned in later thread<BR>I used to also do endDate&#062;Now() as well...<BR><BR>But quickly realized if someone didnt get on and make a new tourney list, nothing would be returned obviously...<BR><BR>This way it will still select the outdated one if no new one exists<BR>then no reports will be missing<BR>just need to be moved to new one when it&#039;s set up<BR>easy enough...<BR><BR>Thanks again Bill<BR>Exactly what I needed.<BR>

8. Senior Member
Join Date
Dec 1969
Posts
11,247

## Yep so I see <nt>

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•