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-

    SystemAddDateReadDateMeterIDReadEntryPrevEntryUsage
    GETDATE()10/1/2006123465002590006002
    GETDATE()9/1/2006123459000575561444
    GETDATE()8/1/2006123457556565541002
    GETDATE()7/1/2006123456554485967958
  • 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 SSC

    GO

    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(D0DATEDIFF(D0[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 (ReadDateMeterID)

    )

    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 (ReadDateMeterIDReadEntryVALUES ('2006-07-01'123456554)

    --SECOND + READS, TRIGGER starts updating the usage column

    INSERT INTO dbo.MetersReads (ReadDateMeterIDReadEntryVALUES ('2006-08-01'123457556)

    SELECT SystemAddDateReadDateMeterIDReadEntryUsage FROM dbo.MetersReads ORDER BY MeterIDReadDate

    --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 (ReadDateMeterIDReadEntryVALUES ('2006-10-01'123465002)

    SELECT SystemAddDateReadDateMeterIDReadEntryUsage FROM dbo.MetersReads ORDER BY MeterIDReadDate

    --corrects both usages for the last 2 months with their valid values

    INSERT INTO dbo.MetersReads (ReadDateMeterIDReadEntryVALUES ('2006-09-01'123459000)

    SELECT SystemAddDateReadDateMeterIDReadEntryUsage FROM dbo.MetersReads ORDER BY MeterIDReadDate

    --INSERT MULTIPLE ROWS FROM MULTIPLE METERS

    INSERT INTO dbo.MetersReads (ReadDateMeterIDReadEntry

    SELECT '2006-11-01' AS ReadDate1234 AS MeterID67000 AS ReadEntry

    UNION ALL

    SELECT '2006-12-01' AS ReadDate1234 AS MeterID71000 AS ReadEntry

    UNION ALL

    SELECT '2006-10-01' AS ReadDate4321 AS MeterID60000 AS ReadEntry

    UNION ALL

    SELECT '2006-11-01' AS ReadDate4321 AS MeterID62000 AS ReadEntry

    SELECT SystemAddDateReadDateMeterIDReadEntryUsage FROM dbo.MetersReads ORDER BY MeterIDReadDate

    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 4 (of 4 total)

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