Previous Record Search (Mileage)

Question

I am trying to put together a report for supply management. I have a Supply Table with the following fields:

DataAbastecimento,
CódigoVeiculo,
KmAbastecimento,
Quantidade,
Valor

In the Report I’m doing, I need to know the KM Traveled...so follow example:

Veiculo Data Abastecimento  KM     KM Anterior
999     02/06/2016          10.000 Buscar o Último KM de Abastecimento de Maio
999     08/06/2016          10.800 10.000
999     11/06/2016          11.300 10.800
999     19/06/2016          13.200 11.300

Until the KM column, it is a simple search...but this KM Anterior is hitting me some time.

Someone has come across this?

Answer 1

I decided to create a FUNCTION for the calculation of the previous KM. The solution becomes more beautiful.

create function dbo.fn_km_abastecimento_anterior
(
  @codigoVeiculo as int,
  @dataAbastecimento as date
)
RETURNS int
AS
BEGIN
  DECLARE @KM int
    SELECT @KM=MAX(a.kmAbastecido)
  FROM abastecimentos a (nolock)
  WHERE a.codigoVeiculo = @codigoVeiculo
    AND a.dataAbastecimento < @dataAbastecimento
  RETURN ISNULL(@KM,0)
END
GO

In your final query, you call your function as a column.

select 
  a.dataAbastecimento,
  a.codigoVeiculo,
  a.kmAbastecido,
  dbo.fn_km_abastecimento_anterior(a.codigoVeiculo, a.dataAbastecimento) as kmAnterior,
  a.qtde,
  a.valor
from abastecimentos a (nolock)