Compute data between record of SQL database within Visual Studio

Question

I am attempting to calculate the miles per gallon for logged fuel full-ups. My table consists 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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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