Get the difference from one record to the one previous

  • In it's simplest form I want a table that I can use to store my Household energy readings, Gas and Electricity.

    I've designed a Table with 3 fields, Date of entry, Electricity meter reading and Gas meter reading. What I would like to

    have is a further 2 fields that are computed by the database that reveals the amount of units used from one month to the

    previous month, I've written a script to create a test table with the results below

    USE test

    GO

    -- Creating Test Table

    CREATE TABLE testtable(dateofReading date, Electricity int, Gas int)

    GO

    -- Inserting Data into Table

    INSERT INTO testtable(dateofReading,Electricity,Gas)

    VALUES('01/Dec/2011','2393','4001')

    INSERT INTO testtable(dateofReading,Electricity,Gas)

    VALUES('03/Jan/2012','2401','4023')

    INSERT INTO testtable(dateofReading,Electricity,Gas)

    VALUES('04/Feb/2012','2411','4034')

    INSERT INTO testtable(dateofReading,Electricity,Gas)

    VALUES('05/Mar/2012','2455','4045')

    Result :-

    2011-12-01 2393 4001

    2012-01-03 2401 4023

    2012-02-04 2411 4033

    2012-03-05 2555 4045

    This is what I'd like to end up with :-

    2011-12-01 2393 4001 0000 0000

    2012-01-03 2401 4023 0008 0022

    2012-02-04 2411 4034 0010 0011

    2012-03-05 2455 4045 0044 0011

    so, if you pick dates 2012-01-03 and 2012-02-04 there's a difference of 11 on the Gas field, I hope this makes sense

    Mick

  • Mick

    This is the logic you need.:

    SELECT
    dateofReading
    , Electricity
    , Gas
    , Electricity - LAG(Electricity,1,Electricity) OVER (ORDER BY dateofReading) AS ElecUsage
    , Gas - LAG(Gas,1,Gas) OVER (ORDER BY dateofReading) AS GasUsage
    FROM testtable;

    However, if you try to create a computed column:

    ALTER TABLE dbo.testtable
    ADD ElecUsage AS (Electricity - LAG(Electricity,1,Electricity) OVER (ORDER BY dateofReading));

    You'll get this error:

    Msg 4108, Level 15, State 1, Line 35

    Windowed functions can only appear in the SELECT or ORDER BY clauses.

    Your best option, therefore, is to use the query above to create a view.

    John

  • Many thanks John,

    An error I'm getting is 'LAG' is not a recognized built-in function name. In case it matters I'm using SQL Server 2008 R2

     

    Mick

     

  • Mick

    I'm afraid it does matter.  Windowing functions were introduced in SQL Server 2012 (I should have noticed which forum you posted in).  You'll need to use ROW_NUMBER to number the rows of the table, then join that result set to itself on RowNo = RowNo + 1 and do the subtraction from there.  This may help you.

    John

  • John,

    Thanks for your last reply, I'm afraid that was far too complicated for me.

     

    Kind regards

     

    Mick

  • ;with cte as 
    (
    select ROW_NUMBER() OVER (ORDER BY x.dateofreading) RowNum,
    *
    from dbo.testtable x
    )
    select a.dateofReading,
    a.Electricity,
    a.Gas,
    a.Electricity-ISNULL(b.Electricity,a.Electricity) ElectricityUsage,
    a.Gas-ISNULL(b.Gas,a.Gas) GasUsage
    from cte a
    left join cte b
    on b.RowNum = a.RowNum - 1
  • Thank you Jonathon,

     

    That's exactly what I needed

     

    Kindest regards

    Michael

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply