FoxPro has date literalsldstart1 = {01/04/03}
...
SELECT ps.*, ;
ps.Passengers1 * oc.Multiplier as Trips1, ;
ps.Passengers2 * oc.Multiplier as Trips2 ;
FROM crsPassengers ps ;
JOIN OpClass oc ;
ON oc.Operator = ps.Operator ;
AND oc.Class == ps.Class ;
INTO CURSOR crsStep2>First thanks very much again for the code. I really din't want to bug you again so I've been struggling to get this to work all day. But I just can't seem to get my head around it. The code I've come up, from your ex., is:
>
>>ldstart1 = ctod("01/04/03")
>ldend1 = ctod("01/05/03")
>ldstart2 = ctod("01/04/04")
>ldend2 = ctod("01/05/04")
>
>SELECT Operator, Route, Class, ;
> SUM(IIF(BETWEEN(date, ldStart1, ldEnd1), Number, 0000000000)) AS Passengers1, ;
> SUM(IIF(BETWEEN(date, ldStart2, ldEnd2), Number, 0000000000)) AS Passengers2 ;
>FROM ETMData ;
>WHERE date BETWEEN ldStart1 and ldEnd1 OR date BETWEEN ldStart2 and ldEnd2 ;
>GROUP BY Operator, Route, Class ;
>INTO CURSOR crsPassengers
>
>SELECT ps.*, ;
> ps.Passengers1 * OpClass.Multiplier as Trips1, ;
> ps.Passengers2 * OpClass.Multiplier as Trips2 ;
> FROM crsPassengers ps ;
> JOIN OpClass oc ;
> ON oc.Operator = ps.Operator ;
> AND ALLTRIM( oc.Class) = ALLTRIM( ps.Class) ;
>INTO CURSOR crsStep2
>
>>
>I can't figure how to get different multipliers to work on the approp. Passenger. I assumed the Join ... On clause would take care of that. If you wouldn't mind kindly considering the samples below and pointing out wher my SQL is erroneous I'd so appreciate it (my SQL was never all that strong on complex cases like this):
>
>>csrPassengers <-------------------------------- This worked well!
>_____________
>Operator Route Class Passengers1 Passengers2
>...
> 1 "0088" "0020" 9 20
> 1 "0088" "0021" 0 16
> 1 "0152" "0020" 6 0
> 1 "0156" "0020" 0 9
> 1 "0158" "0020" 56 100
> 1 "0158" "0021" 20 28
> 1 "0197" "0020" 2 7
> 1 "0197" "0021" 1 0
>...
>
>OpClass
>_______
>Operator Class ... Multiplier
> 1 "0020" 1.50
> 1 "0021" 1.20
>...
>
>and I'm getting:
>
>csrStep2
>________
>Operator Route Class Passengers1 Passengers2 Trips1 Trips2
>...
> 1 "0088" "0020" 9 20 9.00 20.00
> 1 "0088" "0021" 0 16 0.00 16.00
> 1 "0152" "0020" 6 0 6.00 0.00
> 1 "0156" "0020" 0 9 0.00 9.00
> 1 "0158" "0020" 56 100 56.00 100.00
>...
>
>
>
>As you can see in csrStep2, Trips1 and Trips2 are not getting multiplied by the multiplier (or if they are it's always by 1) and, in other permutations of the SQL that I've tried, they were both getting the same multiplier
>
>cheers
>
>Terry
--sb--
