Search Previous Record (Mileage)

Navigation
0

I'm trying to set up a supply management report. I have a Supply Table with the following fields: 

DataAbastecimento,
CódigoVeiculo,
KmAbastecimento,
Quantidade,
Valor

In the Report I am doing, I need to know the KM Traveled ... then follow the 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

To the KM column, it is a simple search ... however this Previous KM is beating me some time.

Has anyone ever come across this?

    
asked by anonymous 15.09.2016 / 18:40

1 answer

0

I solved by creating a FUNCTION for the KM calculation previous. The solution looks 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)
    
15.09.2016 / 20:11
Keep the selected state in edit Get number of entries inside JOptionPane