1. I have a history table where I track the values of a given field each day. It is in this format:

CustomerNumber
TrackingValue
HistoryDate
Status

2. Over the course of tracking this information the tracking value can change from 0 to any number and then back to 0. The data would look like this:

CustomerNumber   TrackingValue   HistoryDate  Status
11111            0               11/1/2005    Active
11111            0               11/2/2005    Active
11111            1000            11/3/2005    Active
11111            1000            11/4/2005    Active
11111            670             11/5/2005    Active
11111            0               11/6/2005    Active

3. I want to know, for any given customer, on any date, if the tracking value goes from a number other than 0 back to 0. In this case, it went from 670 to 0.

select 'For customer number ' + a.customernumber
	+ ' the tracking value went from ' 
	+ cast(b.trackingvalue as varchar(10)) 
	+ ' to 0 on ' 
	+ convert(varchar(11), a.historydate,113)  

from historyTable a 
	inner join historytable b 
	on a.customernumber = b.customernumber 
		and a.historydate > b.historydate

where a.TrackingValue  = 0 and b.TrackingValue > 0
and a.historydate = dateadd(day, 1, b.historydate)

where a.TrackingValue  = 0 and b.TrackingValue > 0
and not exists 
(select historydate from historytable where historydate < a.historydate and historydate > b.historydate)