>
>With Thisform
> ldStartCurr = .dStartDate1
> ldEndCurr = .dEndDate1
> ldStartPrev = .dStartDate2
> ldEndPrev = .dEndDate2
>EndWith
>
>Wait WINDOW "ETM Data: Calculating Numbers of Trips within the Time periods ..." NOWAIT NOCLEAR
>SELECT DISTINCT ps.*, ;
> IIF( ps.CurrPass > 0, oc.Multiplier, 0.00) as Mult2, ;
> IIF( ps.PrevPass > 0, oc.Multiplier, 0.00) as Mult1, ;
> IIF( ps.CurrPass > 0 and oc.Multiplier > 0, ;
> ps.CurrPass * oc.Multiplier, ;
> IIF( ps.CurrPass > 0 and EMPTY( oc.Multiplier), ps.CurrPass, 0000000.00)) as Trips2, ;
> IIF( ps.PrevPass > 0 and oc.Multiplier > 0, ;
> ps.PrevPass * oc.Multiplier, ;
> IIF( ps.PrevPass > 0 and EMPTY( oc.Multiplier), ps.PrevPass, 0000000.00)) as Trips1 ;
>FROM csrETMPassengers ps ;
> JOIN OpClass oc ;
> ON ps.Operator == oc.Operator;
> AND ps.Class == oc.Class ;
> ORDER BY ps.Operator, ps.OpDesc, ps.Route, ps.Class ;
> INTO CURSOR csrStep2 READWRITE
>
>
>This is reporting on use of concessionary bus fares between two date ranges, to see how much more, or less, certain routes are being used. For a year now it's been working fine but a recent permutation of date ranges, and certain OPCLASS records, in a particular set of data, has produced the wrong results.
Okay, here are my first observations:
1) You don't have a WHERE clause so you're not limiting output to any specific dates.
2) In the expressions for Trips2 and Trips1, the inner IIF() seems unnecessary unless CurrPass and PrevPass can be negative.
3) You're going to get one record in the results for each combination of ps and OpClass. If that's not what you want, what do you want?
Tamar
