How to find the value from accumulated balances

Question

In my SQL Server, have a table containing the registration of odometer of vehicles with the value end of each month.

I need to know what the value that each vehicle toured monthly on the basis of those balances/values.

Detail is that there may be months off the books when the vehicle has been stationary all month, so I have to have the registration with value 0 that month.

Layout of Tabela: Registros

| Campos |

Placa_Veiculo (string)
AnoMes (AAAA/MM)
KM_fim_Mes (numerico)

If table I have the following data:

placa=a, 
ano_mes=2019/01, 
km=660; 

placa = a, 
ano_mes=2019/03, 
km=700; 

placa=a, 
ano_mes=2019/04, 
km=780. 

My select has to bring 4 lines thus:

placa=a, ano_mes=2019/01, km=660;
placa=a, ano_mes=2019/02, km=0; 
placa=a, ano_mes=2019/03, km=40; 
placa=a, ano_mes=2019/04, km=80

Answer 1

Here is the solution that deals with the possibility of cars having registration in different periods.

-- código #1
with 
Registros_0 as (
SELECT Placa_Veiculo as placa, 
       min (AnoMes) as ano_mes_inicial, 
       max (AnoMes) as ano_mes_final
  from Registros
  group by Placa_Veiculo
),
Registros_1 as (
SELECT R0.placa, R0.ano_mes_inicial as ano_mes, R.KM_fim_Mes as km,
       convert (date, R0.ano_mes_inicial + '/01', 111) as AnoMes,
       convert (date, R0.ano_mes_final + '/01', 111) as AnoMes_F
  from Registros_0 as R0 
       inner join Registros as R on R.Placa_Veiculo = R0.placa 
                                    and R.AnoMes = R0.ano_mes_inicial
union all
SELECT R1.placa, 
       convert (char(7), dateadd (month, +1, R1.AnoMes), 111),
       coalesce ((SELECT R.KM_fim_Mes 
                    from Registros as R
                    where R.Placa_Veiculo = R1.placa      
                          and R.AnoMes = convert (char(7), dateadd (month, +1, R1.AnoMes), 111)),
                 R1.km),
       dateadd (month, +1, R1.AnoMes),
       R1.AnoMes_F
  from Registros_1 as R1 
  where dateadd (month, +1, R1.AnoMes) <= R1.AnoMes_F                           
)
SELECT placa, ano_mes, km,
       case when km >= lag (km) over(partition by placa order by ano_mes)
                 then (km - lag (km) over(partition by placa order by ano_mes)) 
            else NULL end as Percorrido    
  from Registros_1;                                 

Registraos_0: gets, for each vehicle, which is the first and the last record

Registrations_1: mount, month to month for each vehicle, what is the mileage value. Uses recursive CTE.

---

Another possibility is to use algorithms of gaps and Islands to fill in the gaps and then calculate the distances travelled per vehicle/month.

Answer 2

Try running this script:

SELECT Placa_Veiculo, AnoMes, SUM(KM_fim_Mes)
FROM Registros
GROUP BY AnoMes