Compute data between record of SQL database within Visual Studio

I am attempting to calculate the miles per gallon for logged fuel full-ups. My table consis开发者_运维技巧ts of the flowing:

FillUp(CarID, Date, ODReading, Gallons, StopGo, Highway, FillupID, MPG)

I want to subtract the ODReading from the previous record and divide gallons by this computed value.

How do I work between records to achieve this within Visual Studio 2008's column properties (formula) section?

By itself, the rows in a query resultset do not have any order. There is no "between records".

You will need to use the "ORDER BY" clause to put your rows into a sequence. There are then various tricks you can try. For instance, if you had a "sequence" column, you could do a self-join on B.Sequence = A.Sequence+1. This would give you a single row in the result set that had data from both the "current" and "previous" rows.

It's looks like, that you have picked wrong tool for this task. SQL server and it's interfaces are designed to operate on each row separately.

I would try to do this using managed (c# or vb.net) code.

Why would toy want to do this in C# when you can create a Query for this.

Something like

DECLARE @FillUp TABLE(
        CarID INT, 
        Date DATETIME, 
        ODReading FLOAT, 
        Gallons FLOAT, 
        StopGo FLOAT, 
        Highway VARCHAR(20), 
        FillupID INT, 
        MPG FLOAT
)

INSERT INTO @FillUp (CarID, Date, ODReading, Gallons) SELECT 1, '01 Jan 2010', 100, 20
INSERT INTO @FillUp (CarID, Date, ODReading, Gallons) SELECT 1, '01 Jan 2010', 150, 30
INSERT INTO @FillUp (CarID, Date, ODReading, Gallons) SELECT 1, '02 Jan 2010', 250, 30

;WITH ODOs AS (
    SELECT  *,
            (   
                SELECT  TOP 1 
                        ODReading 
                FROM    @FillUp 
                WHERE   CarID = fu.CarID 
                AND     ODReading > fu.ODReading
                ORDER BY ODReading
            ) NextOD
    FROM    @FillUp fu
)
SELECT  *,
        (NextOD - ODReading) / Gallons CalculatedMPG
FROM    ODOs

You can do this fairly easily using a windowing query:

WITH FillUp_CTE AS
(
    SELECT
        CarID, Date, ODReading,
        ROW_NUMBER() OVER (PARTITION BY CarID ORDER BY Date) AS RowNum
    FROM FillUp
)
SELECT f1.CarID, f1.Date, (f2.ODReading - f1.ODReading) / f1.Gallons AS Mileage
FROM FillUp_CTE f1
INNER JOIN FillUp_CTE f2
    ON f2.CarID = f1.CarID
    AND f2.RowNum = f1.RowNum + 1

You won't be able to do this with a computed column alone. You need to actually write the SQL as above.