SELECT Next Record

[kermitforney]

I have a basic audit/history table that I am querying. The table is pretty basic, the columns are as follows:

ID, Store#, DaysServiced, ChangeDate

A record is inserted every time a store's DaysServiced has been changed.

Basically, what I need from this table is whether or not a store has changed within a given date range. My only problem is that when a value has been changed the present value is kept in the history table as the last change, so I cannot just query the last change record. What I need is to query the date of the last change, but the value of the change before the last change.

I hope this is not too confusing . . . I will explain further.

Table Example with Records
ID, Store#, DaysServiced, ChangeDate

1,1,7,'1/01/2010'
2,1,14,'1/10/2010'

So, when this store was changed to 14 days (which is the present value) a record was inserted into the change table. This is my conundrum, the record is created for the present value not when the present value is changed, but when it is created. The DaysServiced of 14 is the current value and it was changed FROM 7 to 14. The data I am trying to pull is the last change date of '1/10/2010', but pull the DaysServiced for the first record. This is how the query results would read.

Ex. Store#, DaysServiced, ChangeDate
1, 7, '1/1/2010'

 

[kermitforney]

Forgot to add the Faux creation script:

CREATE TABLE [dbo].[tblHistory]
    (
     [lngServiceFreqHistoryID] [int] IDENTITY(1, 1) NOT FOR REPLICATION
                                     NOT NULL
    ,[lngStoreID] [int] NOT NULL
    ,[tntServiceFreqDays] [tinyint] NOT NULL
    ,[datCreated] [datetime] NOT NULL
    )
 
GO

INSERT INTO [dbo].[tblHistory]
           ([lngStoreID]
           ,[tntServiceFreqDays]
           ,[datCreated])
     VALUES
           (1, 
           7,
           '1/1/2010')
GO

INSERT INTO [dbo].[tblHistory]
           ([lngStoreID]
           ,[tntServiceFreqDays]
           ,[datCreated])
     VALUES
           (1, 
           14,
           '1/10/2010')
GO

SELECT lngServiceFreqHistoryID
       ,lngStoreID
       ,tntServiceFreqDays
       ,datCreated
FROM dbo.tblHistory

 

[kermitforney]

Correction:

This is how the query results would read.

Ex. Store#, DaysServiced, ChangeDate
1, 7, '1/10/2010'