Posted by Erland Sommarskog on 07/11/06 21:58

rdraider (rdraider@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]