>Hi Searchers
>
>The below SQL is certainly not getting me the results I expect and, worse still gives me 1 spurious result.
>
>I've already done a search to multiply the number of journies done on eligible bus services, by a certain factor, to give a "trips" total for each bus operator, route no + (ticket) class, et al, within 2 date ranges.
>
>Now I wish to list all the journies that didn't get included in the prev. report because although the (bus) operator had a (ticket) CLASS, the class didn't have an associated MULTIPLIER in the OpClass Table.
>

>OpClass Table is as follows:
>
>Operator#, Class#, Multiplier, ...
>
>And Operator:
>
>Operator#, Class#, ...
>

>
>The ETMDATA table holds details of all Electronic Ticket Machine (ETM) transactions over a period.
>The reason why OpClass needs the 2 keys is that a class is not unique to an operator, and certain class nos mean different things to different operators, but are issued by the ETM manufacturer.
>
>Have I got the NOT EXISTS logic wrong or what is it?
>
>'ppreciate it.
>
>Terry
>
>

>Select	DISTINCT ET.Operator as MLOperator, ET.Class as MLClass, ;
>		op.Desc as MLOpDesc, rf.ServType as MLServType, ;
>		SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS MLCurrPass, ;
>		SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS MLPrevPass ;
>  FROM ETMData ET ;
>	JOIN Operator op ;
>		On ET.Operator 	== op.Code ;
>	JOIN RteFam rf ;
>		On ET.Operator 	== rf.Operator ;
>		and ET.Route    	== rf.ERoute ;
>  WHERE  ET.date BETWEEN ldStartCurr and ldEndCurr ;
>  OR 	ET.date BETWEEN ldStartPrev and ldEndPrev ;
>  AND 	NOT EXISTS ;
>  	( Select * from OpClass ;
>  		WHERE OpClass.Operator == ET.Operator ;
> 		AND   OpClass.Class    == ET.Class ) ;
>  ORDER BY	ET.Operator, ET.Class ;
>  INTO Cursor csrMultless
>
>

Terry,

I have sure of issue but I do know that you should probably rethink using BETWEEN when doing SQL Datetime queries comparison

Declare @ETdate datetime
declare @ldStartCurr datetime
declare @lEndPrev datetime

set @ETdate = '07/20/2006 10:00:00'
set @ldStartCurr='07/20/2006 08:00:00'
set @lEndPrev = '07/20/2006 9:55:00'

if (@ETdate between @ldStartCurr and @lEndPrev)
begin
print 'yes'
end
else
print 'no'

ET.Date would not be considered between ldStartCurr and lEndPrev because is may fall in the date range but it does not fall in range when time is evaluated.

This is just a guess...

Carl