SQL report to show gaps between record entries

Question

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

Your example data has no gaps. Perhaps you could put in a more representative data set along with the results that you want to get back. Also, tag the question with the database you are using. — Gordon Linoff, Nov 05 '14 at 15:02
Additionally is it one table or multiple table (normalized data). — Stephen Bodine, Nov 05 '14 at 15:04
@GordonLinoff Done (tag). Yes gaps are supposed to be rare because it means someone used the car illegally. I added a sample output to the post. — John, Nov 05 '14 at 15:07
@StephenBodine I do have another table with Vehicle Descriptions for each Vehicle Key but that is not important for this report at all, for the gap report it's just that one table as seen in the screenshot. — John, Nov 05 '14 at 15:10
What if two events (for one vehicle) take place on the same day? — wildplasser, Nov 05 '14 at 15:10
`lag(end_od, 1) over (PARTITION BY vehicle_id ORDER BY beg_od)` looks like what you need. (and compare the result to beg_id, obviously) — wildplasser, Nov 05 '14 at 15:13
@wildplasser if a vehicle is used twice on the same day, the same should happen (subtract End odometer of previous record for that vehicle from current Beg Od to get the gap). There will be an individual invoice for each usage. — John, Nov 05 '14 at 15:17

score 2 · Accepted Answer · answered Nov 05 '14 at 16:43

2

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.

answered Nov 05 '14 at 16:43

Gordon Linoff

1,242,037
58
646
786

As a tie-breaker, I would suggest: `PARTITION BY vehicle ORDER BY date, beg_od)` – wildplasser Nov 05 '14 at 18:01
Thanks for introducing me to the lag function! Now all that is left is a column that will do begod - prev_endod to display the gap miles. – John Nov 05 '14 at 18:33

score 0 · Answer 2 · answered Nov 05 '14 at 15:42

 SELECT 
    D1.invoiceNo, 
    D1.vehicalId, 
    CASE WHEN D1.odBeg > D0.odEnd THEN 'MISSING MILES' ELSE 'EXTRA MILES' END AS Notes
 FROM
    (SELECT vehicalId,invoiceNo, RANK() OVER(PARTITION BY vehicalId ORDER BY dateUsed) AS KEY_CUR, odBeg,odEnd FROM @DATA) AS D0
    INNER JOIN 
    (SELECT vehicalId,invoiceNo, RANK() OVER(PARTITION BY vehicalId ORDER BY dateUsed) - 1 AS KEY_LAST, odBeg,odEnd FROM @DATA) AS D1
    ON D0.vehicalId = D1.vehicalId AND D0.KEY_CUR = D1.KEY_LAST
 WHERE
    D1.odBeg <> D0.odEnd

SQL report to show gaps between record entries

2 Answers2