Terry,
I'd start by putting parentheses around "or" "and" blocks of where clause. As I interpret it now:
where ET.date BETWEEN ldStartCurr and ldEndCurr ;
OR ( ET.date BETWEEN ldStartPrev and ldEndPrev ;
AND NOT EXISTS ... )
Probably you meant:
where ( ET.date BETWEEN ldStartCurr and ldEndCurr ;
OR ET.date BETWEEN ldStartPrev and ldEndPrev ) ;
AND NOT EXISTS ...
Cetin
>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
>
>
