Search code examples
sqloracle-databasegaps-in-data

SQL report to show gaps between record entries

I wrote an application where the usage of a number of vehicles is recorded as seen in the screenshot.

I'd like to generate a report for gaps (in miles) between individual usages because the vehicles should not be used other than for travel that is recorded in the application.

A gap would occur when the current record beg odometer - previous record end odometer yields a number greater than 0 -- for that specific car. See the different colored circles. How can I achieve this with sql? I'm using oracle (11g) but I imagine the sql will be similar. Thank you.

Sample output:

  Vehicle V06
  Invoice Date   Dest   Gap 
  123     1/2/14 York   14.0
  122     1/1/14 Pburg   0.0
  Vehicle V05
  Invoice Date   Dest   Gap 
  121     1/3/14 Mill    0.0

vehicle usage database
* I realize I should have used test data that includes a gap, though these should be rare in practice. In such a case, 

  Invoice 67189 would have End Od of 92590 resulting in a GAP of 3.0 miles for 67190
Solution

  • Just use lag():

    select vu.*
from (select vu.*,
             lag(endod) over (partition by vehicle order by date) as prev_endod
      from vehicleusage vu
     ) vu
where begod <> prev_endod;

    Note that the comparison will fail for NULL values, so there is no problem with the first recording for a vehicle.