Link to home
Create AccountLog in
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)Flag for United States of America

asked on

Create New Table of Value Changes

I have a table:

    Key
    ObservationDate
    NumericValue

I want to insert into an empty table one record for each key that experienced a change in value to the latest value.

The empty table:

    Key
    ChangeDate
    BeforeNumericValue
    AfterNumericValue

So if all records for a specific key contain the same numeric value then no new record is created. If one or more changes occurred for a specific key then one new record is created with that key, the date the change occurred, the old value, and the new value.

What is the fastest way to get this done? Can it be done with selects and inserts?

Kevin
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

can you give us an example with dataset..?
Avatar of Matt Bowler
Are you talking about implementing this as an ongoing solution?

In which case you might look at change data capture, or database triggers.

If you are looking at doing adhoc or scheduled runs then I'm sure a query could be built.
Avatar of zorvek (Kevin Jones)

ASKER

I need an SP that I can run after I load the first table.

Sample data:

Key      ObservationDate      NumericValue
A      2012-01-01      1
A      2012-01-02      1
A      2012-01-03      1
A      2012-01-04      1
B      2012-01-01      2
B      2012-01-02      3
B      2012-01-03      3
B      2012-01-04      3
C      2012-01-01      4
C      2012-01-02      4
C      2012-01-03      4
C      2012-01-04      5

New entries:

Key      ChangeDate      BeforeNumericValue      AfterNumericValue
B      2012-01-02      2      3
C      2012-01-04      4      5

Kevin
Create table:

create table tbl_observations([key] char(1), ObservationDate date, NumericValue int)

Test data:

insert into tbl_observations
values ('A','2012-01-01',1)
,('A','2012-01-02',1)
,('A','2012-01-03',1)
,('A','2012-01-04',1)
,('B','2012-01-01',2)
,('B','2012-01-02',3)
,('B','2012-01-03',3)
,('B','2012-01-04',3)
,('C','2012-01-01',4)
,('C','2012-01-02',4)
,('C','2012-01-03',4)
,('C','2012-01-04',5)

Solution query:

with CTE
as
(
      select [key]
      , ObservationDate
      , NumericValue
      ,ROW_NUMBER() OVER (ORDER BY [key],ObservationDate) AS 'RowNumber'
      from tbl_observations
)
select before.[key], [after].ObservationDate, before.NumericValue, [after].NumericValue
from CTE as [before]
inner join CTE as [after]
on [before].Rownumber = [after].Rownumber -1
where before.NumericValue <> [after].NumericValue
and before.[key] = [after].[key]
That's close but it's creating a record for every change in NumericValue. I only want the last change - one record for each Key.

Kevin
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I have not verified that these solutions work. But they look promising.