Sum the differences group by another colomn

Question

I have a table for a vehicle mileage in different states.

Table: VehicleState
VehicleID   Mileage   State          DateTime
   1          3000      TX     2016-09-20 03:00:00
   1          3100      TX     2016-09-20 04:00:00
   1          3200      OK     2016-09-20 05:00:00
   1          3300      OK     2016-09-20 06:00:00
   1          3400      OK     2016-09-20 07:00:00
   1          3500      TX     2016-09-20 08:00:00
   1          3600      TX     2016-09-20 09:00:00
   1          3700      TX     2016-09-20 10:00:00
   1          3800      TX     2016-09-20 11:00:00

I want to get the mileage for that vehicle in each state. For example:

VehicleID  Total_Mileage  State     Date
    1          400         TX      2016-09-20
    1          200         OK      2016-09-20

The first two rows indicate the Vehicle drives at least 100 miles. The third record is 3200, but at that point, I don't know how many miles the vehicle is in TX or OK between the time 4AM to 5AM, I want to ignore the difference between 3100 and 3200. The total mileage in TX will be 100(first two rows) + 300(last four rows), which gives me 400.

I'm currently using a huge stored procedure with cursors to calculate. I'm wondering if there is any easier way to do this. Thank you in advance.

I don't understand how you get those values for your sample data. Can you explain how to actually calculate the `Total_Mileage`? — Lamak, Sep 20 '16 at 17:14
@Serg Because op isn't taking into account the first 2 rows for some reason. That's why I asked for him/her to explain how to get the `Total_Mileage` — Lamak, Sep 20 '16 at 17:20
I see how he gets his results, and this is a `gaps and islands problem`. Google that term to find lots of examples. — Tab Alleman, Sep 20 '16 at 17:20
@TabAlleman but a `gaps and islands` problem would take into account those first 2 rows in the result set, returning another row — Lamak, Sep 20 '16 at 17:22
@Lamak, this is a gaps and islands problem with an additional step of aggregating the islands, grouping by `VehicleID` and `State`. And possibly `Date`. The first 2 rows have a diff of 100. The last 4 rows have a diff of 300, therefore the aggregate for TX is 400. — Tab Alleman, Sep 20 '16 at 17:26
@TabAlleman But the math doesn't work with the sample data if that's the case — Lamak, Sep 20 '16 at 17:27
@Lamak It does, see my edited comment above. 3100-3000=100. 3800-3500=300. 100+300=400. — Tab Alleman, Sep 20 '16 at 17:28
@TabAlleman You are right, for some reason I had seen only 2 rows for `OK`, messing with the way I made the calculations. Thanks for your comment — Lamak, Sep 20 '16 at 17:32
My guess, steps between states shouldn't be ignored. Interpolated or something. Suppose the table has just 3 rows TX,OK,TX. Are you expecting 0,0 as a result? — Serg, Sep 20 '16 at 17:33
Yes, I'm expecting 0,0 as a result if the table has just 3 rows TX, OK, TX. Because at that point, I can't determine how many mileage the vehicle drives in TX and OK, I just want to ignore them. — Jaaaaaaay, Sep 20 '16 at 17:56
@JuanCarlosOropeza I don't want to handle date for now. That's why the records have the same date. — Jaaaaaaay, Sep 20 '16 at 18:05

score 0 · Accepted Answer · answered Sep 20 '16 at 18:22

Following OP comments it looks like row_number() and inner join. Date takes no part in grouping.

declare @t table (
    VehicleID  int,
    Mileage int,  
    State char(2),
    dt  DateTime);

insert @t(VehicleID, Mileage, State, Dt)
values
 (1,3000,'TX','2016-09-20 03:00:00')
,(1,3100,'TX','2016-09-20 04:00:00')
,(1,3200,'OK','2016-09-20 05:00:00')
,(1,3300,'OK','2016-09-20 06:00:00')
,(1,3400,'OK','2016-09-20 07:00:00')
,(1,3500,'TX','2016-09-20 08:00:00')
,(1,3600,'TX','2016-09-20 09:00:00')
,(1,3700,'TX','2016-09-20 10:00:00')
,(1,3800,'TX','2016-09-20 11:00:00');

with nmb as(
    select VehicleID, Mileage, State, rn=row_number() over(partition by VehicleID order by dt)
    from @t
) 
select t1.VehicleID, t1.State, sum(t2.Mileage - t1.Mileage)
from nmb t1
join nmb t2 on t1.VehicleID = t2.VehicleID and t1.State = t2.State and t1.rn = t2.rn-1
group by t1.VehicleID, t1.State;

Sum the differences group by another colomn

1 Answers1