How can i create a computed field that dependent upon a previous entry?
-
Please help -
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!
Kenena
-
No need to have a calculated field. This value will never change so there's no point in recalculating it over and over and over and over and over and over and... (see the point
)?Add an insert trigger that will fetch the last row's value and update to the current row's usage value.
-
Thanks for your response, but I'm still missing the point. The following is what I will need. The Usage field will change for every reading. What I'm trying to achieve is picking up the last reading (as a previous reading) to create the difference calculation. I tried a trigger but don't know how to "fetch the last row's value". Maybe if I just received help on that?
Thanks again-
SystemAddDate ReadDate MeterID ReadEntry PrevEntry Usage GETDATE() 10/1/2006 1234 65002 59000 6002 GETDATE() 9/1/2006 1234 59000 57556 1444 GETDATE() 8/1/2006 1234 57556 56554 1002 GETDATE() 7/1/2006 1234 56554 48596 7958 -
Lol that problem was more complexe than I expected. Anyone can figure out a better solution? I can't imagine that none exists (especially in 2k5). Solution other than to that stuff while reporting / presenting the data?
USE SSCGO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'MetersReads' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.MetersReads
GO
CREATE TABLE dbo.MetersReads
(
SystemAddDate DATETIME NOT NULL CONSTRAINT DF_MetersReads_SystemAddDate DEFAULT (GETDATE())
, ReadDate SMALLDATETIME NOT NULL CONSTRAINT CK_MetersReads_NoTime CHECK ([ReadDate] = DATEADD(D, 0, DATEDIFF(D, 0, [ReadDate]))) --may not be needed but it seems that way with your sample data
, MeterID INT NOT NULL
, ReadEntry INT NOT NULL
, Usage INT NOT NULL CONSTRAINT DF_MetersReads_FirstRead DEFAULT (0)
, CONSTRAINT PK_MetersReads PRIMARY KEY CLUSTERED (ReadDate, MeterID)
)
GO
CREATE NONCLUSTERED INDEX IX_MetersReads_MeterID ON dbo.MetersReads (MeterID)
GO
CREATE TRIGGER dbo.TR_MetersReads_A_I ON dbo.MetersReads
AFTER INSERT
AS
SET NOCOUNT ON
--You can switch to the select statement to see the results of the derived table
UPDATE MAIN
SET Main.Usage = dtUsage.Usage
/*
SELECT
dtUsage.ReadEntry
, dtUsage.MeterID
, dtUsage.PreviousReadDate
, dtUsage.CurrentReadDate
, dtUsage.CurrentRead
, dtUsage.Usage
, Main.ReadDate AS UpdatedRow
*/
FROM dbo.MetersReads Main
INNER JOIN
(
SELECT
MRUsage.ReadEntry
, dtPrvDates.MeterID
, dtPrvDates.PreviousReadDate
, dtPrvDates.ReadDate AS CurrentReadDate
, dtPrvDates.CurrentRead
, dtPrvDates.CurrentRead - MRUsage.ReadEntry AS Usage
FROM dbo.MetersReads MRUsage
INNER JOIN
(
SELECT
MR.MeterID
, MAX(MR.ReadDate) AS PreviousReadDate
, dtResynch.ReadEntry AS CurrentRead
, dtResynch.ReadDate
FROM (
SELECT
MRResynch.MeterID
, MRResynch.ReadDate
, MRResynch.ReadEntry
FROM INSERTED I
INNER JOIN dbo.MetersReads MRResynch
ON I.MeterID = MRResynch.MeterID
AND I.ReadDate <= MRResynch.ReadDate
) dtResynch
INNER JOIN dbo.MetersReads MR
ON dtResynch.MeterID = MR.MeterID
AND dtResynch.ReadDate > MR.ReadDate
GROUP BY MR.MeterID
, dtResynch.ReadEntry
, dtResynch.ReadDate
)
dtPrvDates
ON MRUsage.MeterID = dtPrvDates.MeterID
AND MRUsage.ReadDate = dtPrvDates.PreviousReadDate
) dtUsage
ON Main.MeterID = dtUsage.MeterID
AND Main.ReadDate = dtUsage.CurrentReadDate
GO
--FAIL BECAUSE of the time part of the date
--INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry) VALUES ('2006-12-28 21:24:51.057', 1234, 48596)
--OK
--FIRST READ, TRIGGER DOES NOTHING. This assumes that you will conduct a primary reading when installing the service. Thus giving a starting point to read the usage, hence that usage being 0 (zero) for that row.
INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry) VALUES ('2006-07-01', 1234, 56554)
--SECOND + READS, TRIGGER starts updating the usage column
INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry) VALUES ('2006-08-01', 1234, 57556)
SELECT SystemAddDate, ReadDate, MeterID, ReadEntry, Usage FROM dbo.MetersReads ORDER BY MeterID, ReadDate
--Screw with the data and insert the reads in the wrong order
--usage = 7446 which is correct with the current data
INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry) VALUES ('2006-10-01', 1234, 65002)
SELECT SystemAddDate, ReadDate, MeterID, ReadEntry, Usage FROM dbo.MetersReads ORDER BY MeterID, ReadDate
--corrects both usages for the last 2 months with their valid values
INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry) VALUES ('2006-09-01', 1234, 59000)
SELECT SystemAddDate, ReadDate, MeterID, ReadEntry, Usage FROM dbo.MetersReads ORDER BY MeterID, ReadDate
--INSERT MULTIPLE ROWS FROM MULTIPLE METERS
INSERT INTO dbo.MetersReads (ReadDate, MeterID, ReadEntry)
SELECT '2006-11-01' AS ReadDate, 1234 AS MeterID, 67000 AS ReadEntry
UNION ALL
SELECT '2006-12-01' AS ReadDate, 1234 AS MeterID, 71000 AS ReadEntry
UNION ALL
SELECT '2006-10-01' AS ReadDate, 4321 AS MeterID, 60000 AS ReadEntry
UNION ALL
SELECT '2006-11-01' AS ReadDate, 4321 AS MeterID, 62000 AS ReadEntry
SELECT SystemAddDate, ReadDate, MeterID, ReadEntry, Usage FROM dbo.MetersReads ORDER BY MeterID, ReadDate
GO
--Clean UP
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'MetersReads' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.MetersReads
GO
-
Thanks SO MUCH! This is up and running, and I now have a great deal of study to do to fully understand the implementation.
This is such a great resource and is really appreciated!
Kenena
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply