title: GTFS Database - SQL Queries for "Revenue Mileage" and "Revenue Hours"
body: <p>I'm trying to find the number of revenue miles/kilometers of a "Route" by: day, month, and year; by querying a GTFS database that has the structure described here:</p>
  
  <p><a href="https://developers.google.com/transit/gtfs/reference" rel="nofollow noreferrer">https://developers.google.com/transit/gtfs/reference</a></p>
  
  <p>And see here for a very clear sketch of the structure:</p>
  
  <p><a href="http://blog.openplans.org/2012/08/the-openplans-guide-to-gtfs-data/" rel="nofollow noreferrer">http://blog.openplans.org/2012/08/the-openplans-guide-to-gtfs-data/</a></p>
  
  <blockquote>
    <p><strong>"Revenue distance traveled" definition:</strong></p>
    
    <p>("Available for passengers to use" distance)</p>
    
    <p>The number of miles/kilometers traveled from the first actual bus stop
    where a passenger can board, to the last drop-off at the last bus
    stop, for that particular route and bus run. (then aggregated together
    for all service runs taken by all buses for that particular route)</p>
  </blockquote>
  
  <p>-</p>
  
  <blockquote>
    <p><strong>"Revenue hours" definition:</strong></p>
    
    <p>("Available for passengers to use" time span)</p>
    
    <p>The number of hours from the moment the vehicle arrives at the first
    bus stop, until the moment it drops off its last passenger at the last
    bus stop. (then aggregated together for all service runs taken by all
    buses for that particular route)</p>
  </blockquote>
  
  <p>I'm using SQL Server/MSSQL. Though SQL Lite, or MySQL, or any SQL examples would be perfectly fine.</p>
  
  <p>Basically, I need to be able to SELECT a route, and then correlate the data in the <code>routes</code>, <code>calendar_dates</code>, <code>calendar</code>, <code>stop-times</code>, <code>stops</code>, and <code>trips</code> tables to find how many miles/kilometers were covered from the first stop (<code>stop_times</code> and <code>stops</code> tables) to the last, how many hours elapsed, and find this for a particular <code>service_id</code> (in <code>trips</code> and <code>calendar</code> tables), and then also for all <code>service_id</code>s for a particular route, and be able to get all this for a particular <code>date</code> (in <code>calendar_dates</code> table), or spans of dates (day, month, 3-month period, year, etc).</p>
  
  <p>If a few different queries are needed, that's fine. The revenue distance traveled per route, and the revenue hours per route, can be separate queries.</p>
  
  <p>Has anyone who has done this before be willing to share their query structure for this or has anyone figured this out? Are there any examples of how to write this query? I've been looking everywhere online for weeks.</p>
  
  <p>Here is a diagram image of the database I have created with all relationships shown in detail:</p>
  
  <p><img src="https://i.stack.imgur.com/ieLBl.png" alt="GTFS Database Diagram"></p>