Hi Terry,
Not a single your table has a date field. How do you get Period1/2 w/o it for "No. tickets" and "Multiplier"?
>Not for the faint-hearted.
>
>I don't believe this can be done in 1 SQL statement, nor indeed that it can be done all via SQL, but here goes.
>
>BACKGROUND:
>
>Recently all bus journeys for pensioners in the UK has been made free. Previously they had concessions, such as half-fare. We monitor ticket sales and out s/w claims the unpaid revenue for the bus operators from local councils, etc., who foot the bill.
>
>Since the new free scheme many pensioners will now use a bus for, say, just a few stops, where they wouldn't have when they had to pay, and indeed will use maybe several buses a day.
>
>We have to keep track of the trend. Operators will expect more revenue but we have to allow for the increease in business that they now get which they wouldn't get were the scheme not free. So a "Multiplier" is used. We are comparing a period since the new scheme with the same period last year.
>
>There is table that records all concessionary tickets issued (transaction) per day, per bus route, and class (e.g single, return, weekly) - ETMDATA
>
>ETMDATA
>Operator#, Route#, Class#, No. tickets, Fare per, Value, ...
>
>(several days' worth of these recs within each Op/Route/class perm.)
>
>OPERATOR:
>Operator#, Operator Name, ...
>
>OPERATOR_CLASS
>Operator#, Class#, Multiplier, ...
>
>
>So I have to produce a report of Class, within Route, within Operator, giving:
>- Total for Route
>- Total for Operator
>- Total for the whole scheme
>
>taking in no. tickets (passengers) * multiplier for each class = "Trips", so summing needs to be done)
>
>for both periods, with an indicator of percentage up or down of sales
>
>Below is the approx layout. I can only see that it has to be done with loops and building up result tables, and then for each period. I'll SO owe a beer to anyone who can do this as succinctly as possible.
>
>I'm not sure if I've even explained it adequately but several of you are very skilled at divining someone's meaning from less.
>
>Cap-in-hand
>
>Terry
>
>
> Period 1 Period 2 Period 1 Period 2 Period 1 Period 2 Up/Down
>Operator Route Class No. No.
> Passengers Passengers Multiplier Multiplier Trips Trips
>A 21 14 120 1 120 0
> 15 100 2.5 250 0
> 40 400 1 0 400
> Route total 370 400 8%
>
> 22 14 300 1 300 0
> 40 350 1 0 350
> Route total 300 350 17%
> Operator total 670 750 12%
>B 1 - details ... - 880 1150 31%
>
> 2 120 50 -58%
> Operator total 1000 1200 20%
> Scheme total 1670 1950 17%
>
--sb--