>OpClass Table is as follows: > >Operator#, Class#, Multiplier, ... > >And Operator: > >Operator#, Class#, ... >>
>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 > >When you use OR and AND in WHERE you must use parenthesis to show which must be evaluated first because with that select you will get ALL records which satisfy following condition:
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 ; INNER JOIN Operator op On ET.Operator == op.Code ; INNER JOIN RteFam rf On ET.Operator == rf.Operator and ET.Route == rf.ERoute ; LEFT JOIN OpClass ON OpClass.Operator == ET.Operator AND OpClass.Class == ET.Class WHERE (ET.date BETWEEN ldStartCurr and ldEndCurr OR; ET.date BETWEEN ldStartPrev and ldEndPrev) AND ; OpClass.Operator IS NULL ORDER BY ET.Operator, ET.Class ; INTO Cursor csrMultless