My company needs to write a query that determines when data is being entered inaccurately. We have a table in our db called "ProductChanges". It looks (something) like this -
Product User ChangeDate OldValue NewValue
2344 John 24/01/10 10:00 2344 4324
6435 Greg 28/01/10 13:30 543 6435
6532 Tony 29/01/10 09:45 <NULL> 234
When OldValue is <NULL>
, it means that it is a new product record. We expect there will be a number of changes within the first hour of a record creation, as all the data is updated. But any changes after that are considered to mean the initial record was inaccurately recorded.
SO...what I am trying to come up with is a query that returns all product records created within the last month, that have any subsequent changes recorded an hour after the initial record was created. For output, we want the initial record plus all subsequent changes. Eg. -
Product User ChangeDate OldValue NewValue
6532 Tony 29/01/10 09:45 <NULL> 234
6532 Fred 01/02/10 11:37 234 4324
Thanks in advance!
Update: We are running this database on SQL Server 2000