Jaaaaaaay Jaaaaaaay - 2 months ago 7
SQL Question

Sum the differences group by another colomn

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.

Answer

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;
Comments