SQL Question

[crewchiefpro6]

I thought I had this figured out but apparently not.

I have a table that has the following structure.
RaceSession C
DriverName C
CarNumber C
ElapsedTime N
DialIn N

This file holds an entire race weekend of runs, with Each Driver getting 3 qualifying runs. I need to determine the qualifying position based on the best run for each driver throughout the entire 3 run qualifying sessions.

For Example:
RaceSession = 1 CarNumber= 389A DriverName="Berch" ElapsedTime= 8.844 DialIn= 8.900

RaceSession = 1 CarNumber= 3A DriverName="Derch" ElapsedTime= 8.918 DialIn= 8.900

RaceSession = 1 CarNumber= 367B DriverName="Rierch" ElapsedTime= 8.944 DialIn= 8.900

This would be repeated for RaceSession #2 and #3 also but with different ElapsedTime information.

What is important to determine is WHO is closest to their DialIn time WITHOUT going under it.

Then I built a where clause from a SimpleList OLE (thanks Mike) that the user clicks each qualifying session they want to include in the results.

Soooooo, I created the SQL and where clause that looks something like this

select top 150 racesession, carnumber, drivername, ElapsedTime, DialIn ;
from iCard ;
where racesession = "SUPER COMP T1" and "racesession = "SUPER COMP T2" and "racesession = "SUPER COMP T3" and ElapsedTime >= DialIn ;
order by ElapsedTime ;
group by carnumber

This only works if there is only ONE racesession selected.

If I cannot find another way to do this could I create a seperate cursor from each session and then join the results to see only one driver's best time?

Hope this is not to long. If I could find a way to post the dbf file and a small prg I would.

Don



Don Higgins

http://www.crewchiefpro.com

 

[alvechurchdata]

where racesession = "SUPER COMP T1" and "racesession = "SUPER COMP T2" and "racesession = "SUPER COMP T3"

Shouldn't these ANDs be ORs? They'd have to be in brackets so that you can then "AND" with the ElapsedTime criterion.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[bborissov]

select top 150 racesession, carnumber, drivername, ElapsedTime, DialIn ;
from iCard ;
where racesession IN ("SUPER COMP T1","SUPER COMP T2","SUPER COMP T3") and ElapsedTime >= DialIn ;
order by ElapsedTime ;
group by carnumber

Borislav Borissov

 

[crewchiefpro6]

Thank you Brislav and Geoff for responding. Here is what I have found so far;

SELECT top 200 * from icard wher session in("SUPER COMP T1") and iquarter >= idialin order by IQUARTER group by carnumber

works fine for one session. If I add another session to the IN clause then it gives indorrect information and order. Seems if more than one IN clause is used the section "and iquarter >= idialin " is not even looked at.

Any further ideas would be helpful.

If I cannot get this approch to work then I am going to switch gears so to speak and try 4 seperate commands, one for each session and then join the tables based on the requirements, do you think that would work better?



Don Higgins

http://www.crewchiefpro.com

 

[alvechurchdata]

Seems if more than one IN clause is used the section "and iquarter >= idialin " is not even looked at.

That's unusual behaviour and must be being caused by something else. Two things come to mind:

Where does iquarter come from?

What is GROUP BY doing? You've got no aggregates in the SQL so there's nothing to group.

Strip the SQL down to its bare essentials - no TOP, no ORDER, no GROUP:

select racesession, carnumber, drivername, ElapsedTime, DialIn ;
from iCard ;
where racesession IN ("SUPER COMP T1","SUPER COMP T2","SUPER COMP T3")

then add clauses one by one until something goes wrong.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[crewchiefpro6]

Geoff,
I did remove everything and it worked to find this information, but will not perform the next line "and iquarter >= idialin".

I then broke the select statement into 2 queries. One to select the IN clause, then another one to select from the first cursor like the code below and it works fine.

** first select below just to get the IN clause information
SELECT * FROM ICARD WHERE SESSION IN &lcAnswer into cursor csrAnswer

** second query to find all the top qualifiers and move them into another cursor
SELECT TOP 250 DRIVERNAME, IQUARTER, CARNUMBER, SESSION, idialin ;
FROM csrAnswer ;
WHERE csrAnswer.IQUARTER >= csrAnswer.IDIALIN ;
ORDER BY IQUARTER ;
group by carnumber ;
INTO cursor csrQualifying
IF _TALLY > 0
**Browse Normal NoCaption
ENDIF

3rd query to find the lower half qualifiers and make sure no duplicate carnumbers exist from a comparison of the 2 previous queries
SELECT top 250 csrAnswer.DRIVERNAME, csrAnswer.IQUARTER, ;
csrAnswer.CARNUMBER, csrAnswer.SESSION, csrAnswer.idialin ;
FROM csrAnswer, csrQualifying ;
WHERE csrAnswer.CarNumber not IN(SELECT CarNumber from csrQualifying) ;
.and. csrAnswer.IQUARTER < csrAnswer.IDIALIN ;
order by csrAnswer.iquarter desc ;
group by csrAnswer.carnumber ;
INTO cursor csrQualifyingBottom

Since there are less than 5000 rows normally these 3 queries execute in under 1 second and that works fine for my purposes.

Don



Don Higgins

http://www.crewchiefpro.com

 

[alvechurchdata]

that works fine for my purposes

Can't understand why the original wasn't working but I'm glad you've found a way round the problem. Personally I prefer to write queries as several simple steps like that - they're so much easier to debug than a single monolithic slab of SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk