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 crsStep2I 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