Jaaaaaaay Jaaaaaaay - 1 year ago 43
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 Source

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;