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
March 30, 2020 at 3:05 pm
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
March 30, 2020 at 3:14 pm
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
March 30, 2020 at 3:22 pm
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
March 30, 2020 at 4:23 pm
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
March 30, 2020 at 9:00 pm
Thank you Jonathon,
That's exactly what I needed
Kindest regards
Michael
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply