how to get pervious record?

abdusalam · July 6th, 2004, 06:49 AM

Hi,

   i have boiled my head morning to evening but could not make any solution nor logic....

    Poblem is

   I have one table with following columns

VehicleName /* Simple Vehicle Name */
FuelQty /* Filled Fuel Qty */
MeterReading /* Meter Reading at Filling time */
CDate /* Current Date of filling vehicle */

   Now i need following results inculding above columns in report...

   KiloMeters /* formula is KiloMeters =(Current MeterReading) - (Last MeterReading ) */
   AVG /* Formula is AVG =(KiloMeters % FuelQty )

....

   Hope you have gotten problem...

thanks

Stay Beautiful,
Abdul Salam

planoie · July 6th, 2004, 11:13 AM

To get "Last MeterReading", you will need to construct a subquery that ties into the main query to get the "previous" record.

happygv · July 6th, 2004, 01:01 PM

Hi Abdul,

I am trying to give you a step-by-step logic here. Not sure if this is what you are trying to achieve.

Code:

Declare @CurrentMeterReading INT
SET ROWCOUNT 1
select @CurrentMeterReading = MeterReading from YOURTABLE where VehicleName = "SOMETHING" order by CDATE desc

Declare @LastMeterReading INT
Declare @FuelQty INT
SET ROWCOUNT 1
select @LastMeterReading = MeterReading, @FuelQty=FuelQty from YOURTABLE where CDate < (Select Max(CDate) from YOURTABLE) order by CDATE desc

Declare @KiloMeters INT, @Avg INT
Select @KiloMeters = @CurrentMeterReading - @LastMeterReading
Select @Avg = @KiloMeters / @FuelQty

Select Kilometers=@KiloMeters, Average=@Avg
SET ROWCOUNT 0

You can test this out to see if this output is what you are looking for. If so you can simplify this logic as needed.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

abdusalam · July 8th, 2004, 02:23 AM

Thank you Vijay.
   But i have done it throgh a long and tempraroy way ... you may say with a trick( with creating new 3 tables
    and 4 views one by one .....very headech ) okay

   Your given code can hlep me .. but how i can loop from BOF to EOF ....
   you may check images on http://www.geocities.com/askhamosh/prob.htm
   hope you will come with greate help ...

thanks

Stay Beautiful,
Abdul Salam

happygv · July 8th, 2004, 02:12 PM

Hi there,

You can put this code under a stored procedure and execute that within your ASP pages if any.

Code:

Create procedure MYPROC (@VehicleName varchar(100)=NULL)
as
Declare @CurrentMeterReading INT
SET ROWCOUNT 1
select @CurrentMeterReading = MeterReading from YOURTABLE where VehicleName = @VehicleName order by CDATE desc

Declare @LastMeterReading INT
Declare @FuelQty INT
SET ROWCOUNT 1
select @LastMeterReading = MeterReading, @FuelQty=FuelQty from YOURTABLE where VehicleName = @VehicleName and CDate < (Select Max(CDate) from YOURTABLE) order by CDATE desc

Declare @KiloMeters INT, @Avg INT
Select @KiloMeters = @CurrentMeterReading - @LastMeterReading
Select @Avg = @KiloMeters / @FuelQty

Select Kilometers=@KiloMeters, Average=@Avg
SET ROWCOUNT 0
Return

Code:

Execute MYPROC "VehicleName"

I think this is what you are looking for.
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

abdusalam · July 9th, 2004, 02:45 AM

Hi, Sorry this not that mean ...

i'm going on leave see you later

Stay Beautiful,
Abdul Salam

abdusalam · July 19th, 2004, 05:01 AM

Thank you Vijay ... i have solved by your code with some change....

Stay Beautiful,
Abdul Salam