How to only get changed column rows from a table

Transact-SQL
1

I have the following Audit table for Stock Items

ItemHistory
ItemNo,LogNo,Price,Date, and some other columns

Each time an item in my table gets updated it writes to this table for every ItemNo change it increments the LogNo for that Item.

So the data consists of (simplified)

001,1,100,2015/01/01
001,2,100,2015/01/04
001,3,200,2015/01/07
002,1,110,2015/01/01
002,1,120,2015/01/10
002,2,120,2015/01/04
002,3,120,2015/01/06
002,4,120,2015/01/30
etc
I need a date range for the price changes for item.
So I want output as
ItemCode,Pricem,DateStart,DateEnd
001,100,2015/01/01,2015/01/07
001,200,2015/01/07,todaysdate(2015/05/15)
002,110,2015/01/01,2015/01/10
002,120,2015/01/10,todaysdate(2015/05/15)

The audit table is very large so I want the best performance type query.

Can someone point me in the right direction?

2

I don't suppose the LogNo is guaranteed to be sequential contiguous - with no gaps - is it?

If it were then

SELECT H1.ItemNo, H2.Date, H1.Price as [OldPrice], H2.Price as [NewPrice]
FROM ItemHistory AS H1
     JOIN ItemHistory AS H2
ON H2.ItemNo = H1.ItemNo
AND H2.LogNo = H1.LogNo + 1 
AND H2.Price <> H1.Price

would give the dates on which price had changed.

If LogNo is not contiguous then the logic to find the "Next record" is a bit more complex and there are folk here much more able than me to suggest a method that will perform well.

EDIT: Having re-read the question you don't need the date of change, but rather than earliest date of old-price and the first-date of new price, so you'll need an efficient "next record" method to achieve that anyway.

3

If anyone wants some sample data:

CREATE TABLE #ItemHistory
(
	ItemNo int,
	LogNo int,
	Price int,
	[Date] date
)

INSERT INTO #ItemHistory
VALUES
(001,1,100,'20150101'),
(001,2,100,'20150104'),
(001,3,200,'20150107'),
(002,1,110,'20150101'),
(002,1,120,'20150110'),
(002,2,120,'20150104'),
(002,3,120,'20150106'),
(002,4,120,'20150130')

SELECT *
FROM	#ItemHistory

DROP TABLE #ItemHistory

@madlo please provide this in future, it saves us all spending the time to reinvent the same wheel.

4

Thank Kirstens. Will do so for future The LogNo is always in order for an item. So it always goes in time sequence. Take note that you might have a scenario where you have two changes on one day for the item. Then it should take the last one i.e. the last log no for that day.

Yes I need an efficient next record query

5

Maybe this:

select itemno
      ,price
      ,date
      ,row_number() over(partition by itemno order by date,logno) as rn
  into #tempitemhist1
  from itemhistory
;

select a.itemno
      ,a.price
      ,a.date
      ,row_number() over(order by a.itemno,a.rn) as rn
  into #tempitemhist2
  from #tempitemhist1 as a
       left outer join #tempitemhist1 as b
                    on b.itemno=a.itemno
                   and b.rn=a.rn-1
                   and b.price=a.price
 where b.ItemNo is null
;

select a.itemno
      ,a.price
      ,a.date as startdate
      ,isnull(b.date,getdate()) as enddate
  from #tempitemhist2 as a
       left outer join #tempitemhist2 as b
                    on b.itemno=a.itemno
                   and b.rn=a.rn+1
 order by a.itemno
         ,a.rn
;

drop table #tempitemhist2;
drop table #tempitemhist1;