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%