How to combine 2 records into 1 unique record

[rdraider]

Hi all,

We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.

The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.

I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.
example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'


So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.

Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.

Thanks for your time.

[Erland Sommarskog]

rdraider (rd******@sbcglobal.net) writes:

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
So I need a way to combine these 2 log entries into a unique occurance.
The ordernr and syscreated could be used to link records. syscreated
always appears to be the same for the 2 log entries down to the second.
Selcode can change from NULL to a number of different values or back to
NULL.Status is either 'A' for approved or 'O' for open. An order can
have many log entries during its life. The selcode may be changed
several times for the same order.

Ideally, I would like a result that links 2 log entries and shows the
status changed from 'A' to 'O' when selcode changed.

Could this do:

SELECT a.ordernr, a.syscreated,
oldselcode = a.old_value, newselcode = a.new_value,
oldstatus = b.old_value, newstatus = b.new_value
FROM ordlog a
JOIN ordlog b ON a.ordernr = b.ordernr
AND datediff(seconds, a.syscreated, b.syscreated) <= 1
WHERE a.varname = 'selcode'
AND b.varname = 'status'
AND coalesce(a.old_value, '') <coalesce(a.new_value, '')
AND a.old_value = 'A'
AND b.new_value = 'B'

Note: this is an untested query.

If the does not return the expected results, I suggest that you post:

o CREATE TABLE statments for the involved table(s).
o INSERT statements with sample data.
o The desired output given the sample.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx