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