Running total based on indicator column, SQL Server

Question

I wanted to calculate a running total which adds up when the FuelIndicator switches from NotReserve to Reserve. Can anyone suggest how i will be able to achieve that?

FuelDate    FilledBy    KMSreading  FuelAmt FuelPrice   Vehicle         FuelIndicator
2017-04-12  Acb         34040       200     71.29       Suzuki GS150R   Reserve
2017-04-17  Acb         34142       200     71.29       Suzuki GS150R   Reserve
2017-04-20  Acb         34246_______200_____72.97_______Suzuki GS150R   Not Reserve
2017-04-24  Acb         34350_______200_____72.97_______Suzuki GS150R   Not Reserve
2017-04-28  Acb         34365_______100_____72.97_______Suzuki GS150R   Reserve
2017-04-30  Acb         34500       100     72.97       Suzuki GS150R   Reserve
2017-05-03  Acb         34620       250     75.20       Suzuki GS150R   Reserve

Expected Output:

FuelDate    FilledBy    KMSreading  FuelAmt FuelPrice   Vehicle         FuelIndicator
2017-04-12  Acb         34040       200     71.29       Suzuki GS150R   Reserve
2017-04-17  Acb         34142       200     71.29       Suzuki GS150R   Reserve
2017-04-28  Acb         34365_______500_____72.97_______Suzuki GS150R   Reserve
2017-04-30  Acb         34500       100     72.97       Suzuki GS150R   Reserve
2017-05-03  Acb         34620       250     75.20       Suzuki GS150R   Reserve

Thanks,

Answer 1

You seem to want to combine "not reserve" with the following "reserve"s. This seems to assume that all other columns are the same, except fuel amt, kms reading, and date.

You need to define a group. In this case, the hard part is getting the extra "reserve" with the previous "not reserve"s. The key idea is to sum the number of "reserve" values from each row to the end of the data. This method will assign the groups, just as you want them:

select t.*,
       sum(case when fuelindicator = 'Reserve' then 1 else 0 end) over (partition by vehicle order by fueldate desc) as grp
from t;

Once we have the grouping, the rest is aggregation:

select max(fueldate) as fueldate, FilledBy, max(KMSreading) as KMSreading,
       sum(FuelAmt) as fuelamt, FuelPrice, Vehicle,
       'Reserve' as fuelindicator
from (select t.*,
             sum(case when fuelindicator = 'Reserve' then 1 else 0 end) over (partition by vehicle order by fueldate desc) as grp
      from t
     ) t
group by FilledBy, FuelPrice, Vehicle ;