He Sergey

Sorry, I've been busy on other stuff today. Thanks for the SQL.

How to pull "multiplier" from the the Op-class? Well the class code is given to the operators by the ETM manufacturer, in hex form, and is programmed into the ETM. Now one operator may use code XXX to mean "half-day cheap return" whereas another may use it for "county youth card", for instance. Hence, a code isn't unique but can only be compounded with the op # to give an unique key. So it's a matter of:

from the ETMDATA rec, getting the class# and op#
Using this to find the approp. class on the Op-Class table and thus the multiplier

SO how could the getting of that be slotted into your SQL ? :-)

(PS Have I told you I love you today? :-)

>Hi Terry
>
>I still don't understand how to pull "multiplier" from the the Op-class table because of multiple records per op# and class#. Anyway, below is a basic query that should get you started. You can calculate totals/%'s in the report itself using the group footers/Report variables.

>SELECT Operator, Route, Class, ;
>	SUM(IIF(BETWEEN(etmdate, ldStart1, ldEnd1), Notickets, 0000000000)) AS Passengers1, ;
>	SUM(IIF(BETWEEN(etmdate, ldStart2, ldEnd2), Notickets, 0000000000)) AS Passengers2 ;
>FROM ETMData ;
>WHERE etmdate BETWEEN ldStart1 and ldEnd1 OR etmdate BETWEEN ldStart2 and ldEnd2 ;
>GROUP BY Operator, Route, Class
>

>
>>
>>Sorry to confuse you. I'm the hell confused myself. This is an arcane legacy system that I'm just getting to grips with. The ETMDATA table has been compiled from raw ticket machine records (that come in hex format) and sums sales for each class within op within date. Now "that's dealt with in the pre-processing of the raw ETM data that gets compiled into out ETMDATA table" meant that the Op-class table's classes would be checked to ensure that the class was in force on the date of the ticket
>>
>>i.e. 10 pensioners rode on July 4, each with a class 14/17 ticket - produces 10 ETM hex recs. Our post processing produces 1 ETMDATA rec summing those 10 (then an average is worked out or something - I don't know the processing. UI'm having to ask the clerks who deal with this old system what al this means.
>>
>>Now the multiplier field has only recently been added to the Op-class table, especially for this report. All new ETM recs since the free tickets will have a multiplier of 1, i.e. their class will have a xplier of 1.0
>>
>>You can see from the report layout that operator A, class 15: 100 paqssengers, multiplier of 2.5 -> 250 "trips"
>>