computed field

Question

How do you create a computed field that is dependent upon a previous entry?  My table – for monthly meter reading entries

SystemAddDate       ReadDate      MeterID          ReadEntry      PrevEntry       Usage

How do I reference the last entry so I can create a usage amount between the last two entries?

Thanks for your help – you guys have taught me SO MUCH!

Answer 1

What is the reason for computed column? How about just computing it in the query?

Take a look at UDF computed column:

http://blogs.msdn.com/b/sqlcat/archive/2011/11/28/a-computed-column-defined-with-a-user-defined-function-might-impact-query-performance.aspx

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

try this,

Select SystemAddDate, ReadDate, MeterID,
((select PreviousEntry from Table p where p.readdate-1 = r.readdate) -  r.PreviousEntry) as ComputedField
from table r

please give us some sample records and sample queries that you are trying ,  so that we can work on it..

Thanks,

---

Dineshkumar Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

Answer 3

I created a table containing:-
MeterID ReadingDate Entry
1 2012-04-01 5674
2 2012-05-01 492
3 2012-06-01 6542
1 2012-08-01 5924
2 2012-12-01 756
3 2013-02-01 6841
Hope this is close enough to what you have

Write a couple of scalar functions:-

CREATE FUNCTION [dbo].[ufs_previousDate]
(
    @MeterID int,
    @ReadingDate date
)
RETURNS date
AS
BEGIN
  DECLARE @PreviousDate date

  SELECT Top(1) @PreviousDate = ReadingDate
    FROM dbo.tblReading
    WHERE MeterID = @MeterID
      AND ReadingDate < @ReadingDate
    ORDER BY ReadingDate DESC

  RETURN @PreviousDate

END

CREATE FUNCTION ufs_previousEntry
(
    @MeterID int,
    @ReadingDate date
)
RETURNS int
AS
BEGIN
  DECLARE @PreviousEntry int

  SELECT Top(1) @PreviousEntry = Entry
    FROM dbo.tblReading
    WHERE MeterID = @MeterID
      AND ReadingDate < @ReadingDate
    ORDER BY ReadingDate DESC

  RETURN @PreviousEntry

END

Now you can write a view like this:-

SELECT   MeterID,
ReadingDate,
Entry,
dbo.ufs_previousDate(MeterID, ReadingDate) AS PreviousDate, 
dbo.ufs_previousEntry(MeterID, ReadingDate) AS PreviousEntry,
Entry - dbo.ufs_previousEntry(MeterID, ReadingDate) AS Usage,
DATEDIFF(day, dbo.ufs_previousDate(MeterID, ReadingDate), ReadingDate) AS Period
FROM dbo.tblReading

MeterID ReadingDate Entry PreviousDate PreviousEntry  Usage Period
1 2012-04-01 5674 NULL NULL NULL NULL
2 2012-05-01 492 NULL NULL NULL NULL
3 2012-06-01 6542 NULL NULL NULL NULL
1 2012-08-01 5924 2012-04-01 5674 250 122
2 2012-12-01 756 2012-05-01 492 264 214
3 2013-02-01 6841 2012-06-01 6542 299 245

Answer 4

Sorry about the formatting

Answer 5

In my opinion, computed columns are not meant for that. I would only use a computed column if all required information is either static or part of the information in the row.

I wouldn't recommend creating a scalar UDF for it (as suggested by another responder), because they perform poorly when you select multiple rows.

The suggestion to create a view is a good one. In the view, you could use a scalar subquery, or possibly a Left Outer Join with itself. Whether the method of an Outer Join would work for you depends on the way you determine the "previous entry". Using a scalar subquery will definitely work, but the Outer Join has a better chance of leading to good performance.

If you need more help, then please explain how a previous entry is determined, preferably with some sample data and desired outcome.

---

Gert-Jan

Answer 6

>> How do you create a computed field [sic: columns are not fields] that is dependent upon a previous entry?  My table – for monthly meter reading entries <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

CREATE TABLE Meter_Readings
(meter_id CHAR(10) NOT NULL,
 reading_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
 PRIMARY KEY (meter_id, reading_date),
 current_reading DECIMAL(8,0) NOT NULL,
 prior_reading DECIMAL(8,0) NOT NULL);

You do not need a computed column. Do the insertions with a procedure that gets the prior reading:

CREATE PROCEDURE Post_Meter_Reading
(@in meter_id CHAR(10), @in_reading_date DATE, @in_current_reading DECIMAL(8,0))
AS
INSERT INTO Meter_Readings
VALUES (@in meter_id, @in_reading_date, @in_current_reading,
         (SELECT LAST (R1.current_reading)
                 OVER (PARTITION BY R1.meter_id ORDER BY R1.reading_date
            FROM Meter_Readings AS R1));

untested

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL