March 8, 2010 at 8:09 am
Hi. I have 2 tables I wish to compare.
Products
ProductHistory
The current record in Products is written to the ProductHistory table prior to any modification.
Product Table PK = Product
ProductHistory Table PK = ProductHistory
ProductHistory will contain multiple records for each product.
I would like to compare the two tables to isolate the modification that was made.
Any ideas?
Kind Regards,
Phil.
PS: Would sample data, table structures help?
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 8, 2010 at 9:00 am
2Tall (3/8/2010)
Hi. I have 2 tables I wish to compare.PS: Would sample data, table structures help?
yeah, to get you an intelligent example, instead of a crappy peudocode example, we'd need the actual definitions. I'm guessing a comparison with row_number will be what you want, so you cna see each row that changed, but lets see the structure to be sure.
Lowell
March 8, 2010 at 9:24 am
OK, will post ASAP.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 8, 2010 at 1:25 pm
Please find attached sample code script.
The script will create the 2 required tables + sample data.
Products contains a single product (the current product)
ProductsHistory contains that ProductId prior to each modification.
I would like to return only the changes that have occured against the product (so the values that exist in ProductsHistory table that differ to the values currently held against the Product in Products).
Hope that make sense.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 9, 2010 at 3:21 am
I might be oversimplifying here but isn't it the case that products will have only one record for a particular product id and all the past records are written to the producthistory table.
Is this what you are looking for?
select * from ProductsHistory
where Product in
(Select distinct Product
from Products )
order by Product,LastModifiedDate
Thanks
Pankaj
March 9, 2010 at 6:14 am
2Tall,
Could you confirm the expected output based on the sample data please?
If it is not obvious from the sample, please also explain the rules for producing the output.
Thanks
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 6:24 am
My question is similar to Paul's; what is it you want to see;
if you look at the data, which changes do you want to track? if you ignore the user and the modified date/time, the only change is the toggling of the there Column EngineeringOnly changes from 1 to 0; you can use the other data to track who and when it happened; this might not be representative of what you want to track for changes.
for example, this will produce a change list history:
--results:
RecordType Product ProductId ProductDescription EngineeringOnly CreatedUser CreatedDate LastModifiedUser LastModifiedDate
----------- -------- ------------ -------------------------- --------------- ------------ ----------------------- -------------------- -----------------------
PrimeRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 1 CONVERT 2009-02-16 11:28:00.000 Steve_Smith 2010-02-28 02:01:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 CONVERT 2009-02-16 11:28:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-03-11 17:50:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-04-19 02:01:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-06-07 02:01:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-07-06 17:14:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-10-04 02:00:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2009-12-20 02:00:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2010-01-17 02:01:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 0 CONVERT 2009-02-16 11:28:00.000 xxxxx 2010-01-25 10:36:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 1 CONVERT 2009-02-16 11:28:00.000 xxxxx 2010-02-15 10:52:00.000
ChildRecord 55085 11-720-6610 BEAM FORMING NETWORK ASSY. 1 CONVERT 2009-02-16 11:28:00.000 Steve_Smith 2010-02-22 12:44:00.000
this was the simple SQL:
select'PrimeRecord' As RecordType, dbo.Products.* from dbo.Products
UNION ALL
select 'ChildRecord' As RecordType,Product,ProductId,ProductDescription,EngineeringOnly,CreatedUser,CreatedDate,LastModifiedUser,LastModifiedDate from
dbo.ProductsHistory
order by RecordType DESC,Product,LastModifiedDate ASC
Lowell
March 9, 2010 at 7:22 am
Hi, apologies for the day in posting back I blame those end users!
Yes Products will only contain a single instance for each product.
The example code I posted was fairly simple but in essence I want to return only the changes that have been applied to the Product (including the person who made the change).
The Products/ProductsHistory tables contain 70 + columns. When a user modifies the Product record a snapshot of the product record is saved to ProductsHistory table before the change is recorded. It may be that only 1 column has changed, however all columns are written. It is only the change I wish to output for each ProductHistory record.
I suppose each ProductHistory record can be compared against the current ProductRecord and where the columns differ output only the columns where there is no match.
Hope that helps,
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 9, 2010 at 8:13 am
Phil,
Yes, I think I understand. But how about just one example of sample output? Do you want one row per detected change per product, all changes on one row with only the columns that changed...and so on. It makes a big difference to the code.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 9, 2010 at 1:09 pm
Hi Paul. I would want one row per record (ProductHistory).
This is how it works....
User changes Product record. Prior to the Product record being updated the current product values are saved to the ProductHistory table.
The oldest ProductHistory record will be the original status of the Product prior to any change. The Product record will display the current status of the Product.
The second oldest ProductHistory record will in effect show any changes from the oldest ProductHistoryRecord, the third oldest ProductHistory record will show the changes applied after the second update so on and so forth.....the last ProductHistory record will show any changes that were applied to the current Product record.
I think I have explained that correctly!
Does that help any?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 9, 2010 at 7:14 pm
Ok, so I'm going to proceed on the basis that you just want to see one row per product, and that row should contain values for just the columns that have changed at any point (even if they were changed and subsequently changed back to the original value). To produce a coherent result set for many different products, the result set will include a NULL in a column if the value has never changed. Unless you tell me that is wrong, I will try to knock some code up for this a bit later today.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 1:03 am
Thanks Paul.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 10, 2010 at 1:10 am
WITH Selection
AS (
-- The Product History records to summarize
-- Columns converted to SQL_VARIANT for a later UNPIVOT operation,
-- which requires all inputs to have the same data type
SELECT ProductHistory,
ProductId = CONVERT(SQL_VARIANT, ProductId),
ProductDescription = CONVERT(SQL_VARIANT, ProductDescription),
EngineeringOnly = CONVERT(SQL_VARIANT, EngineeringOnly),
CreatedUser = CONVERT(SQL_VARIANT, CreatedUser),
CreatedDate = CONVERT(SQL_VARIANT, CreatedDate),
LastModifiedUser = CONVERT(SQL_VARIANT, LastModifiedUser),
LastModifiedDate = CONVERT(SQL_VARIANT, LastModifiedDate)
FROM ProductsHistory
WHERE Product = 55085
),
KeyValuePairs
AS (
-- UNPIVOT each history row into key-value pairs
-- e.g. EngineeringOnly = 0 becomes two columns
-- Name = 'EngineeringOnly'; Value = 0;
SELECT KeyValuePairs.ProductHistory,
KeyValuePairs.Name,
KeyValuePairs.Value
FROM Selection
UNPIVOT (
Value
FOR Name IN
(
-- List of columns to produce audit trail for
ProductId,
ProductDescription,
EngineeringOnly,
CreatedUser,
CreatedDate,
LastModifiedUser,
LastModifiedDate
)
) KeyValuePairs
),
ChangeList
AS (
-- The previous step generates duplicates for each history
-- row where a given value did not change (but something else did).
-- This CTE selects just the most recent example of each combination
-- of Name and Value. We also assign a change_sequence number here,
-- and count the number of changes for each key name.
SELECT change_sequence =
ROW_NUMBER()
OVER (
PARTITION BY KVP.Name
ORDER BY KVP.ProductHistory DESC),
records =
COUNT(*)
OVER (
PARTITION BY KVP.Name),
KVP.Name,
KVP.Value,
KVP.ProductHistory
FROM KeyValuePairs KVP
WHERE ProductHistory =
(
SELECT MAX(KVP2.ProductHistory)
FROM KeyValuePairs KVP2
WHERE KVP2.Name = KVP.Name
AND KVP2.Value = KVP.Value
)
)
SELECT CL.Name, -- Name of the column that changed
CL.change_sequence, -- Sequence number of the change
CL.Value, -- New value
CL.ProductHistory -- Primary Key from the audit table
FROM ChangeList CL
-- There must be at least two records
-- for any change in value.
WHERE CL.records > 1
ORDER BY
CL.Name,
CL.change_sequence;
Output:
Name change_sequence Value ProductHistory
==== =============== ===== ==============
EngineeringOnly 1 1 20982
EngineeringOnly 2 0 19316
LastModifiedDate 1 2010-02-22 12:44:00.000 20982
LastModifiedDate 2 2010-02-15 10:52:00.000 20405
LastModifiedDate 3 2010-01-25 10:36:00.000 19316
LastModifiedDate 4 2010-01-17 02:01:00.000 19046
LastModifiedDate 5 2009-12-20 02:00:00.000 18030
LastModifiedDate 6 2009-10-04 02:00:00.000 14526
LastModifiedDate 7 2009-07-06 17:14:00.000 10128
LastModifiedDate 8 2009-06-07 02:01:00.000 8520
LastModifiedDate 9 2009-04-19 02:01:00.000 6033
LastModifiedDate 10 2009-03-11 17:50:00.000 3808
LastModifiedDate 11 2009-02-16 11:28:00.000 1935
LastModifiedUser 1 Steve_Smith 20982
LastModifiedUser 2 xxxxx 20405
LastModifiedUser 3 CONVERT 1935
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 2:26 am
Paul many thanks, great work. I ran against a test system. To confirm the output (see attached).
The first 2 rows returned the column name that changed (EngineeringOnly) Row 1 Value = 0, Row 2 Values = 1.
There are then 15 rows referencing the LastModifiedDate.
There are 4 rows referencing the LastModifiedUser.
Question. How might I relate the user who made the modification to the column that was modified?
Once again many thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 10, 2010 at 4:13 am
Hey Phil,
You are right - it doesn't seem sensible to include change details for the informational Date and User columns.
I have updated the example code to match - all I did was to comment those columns out from the UNPIVOT, and add them in to the SELECT clauses where appropriate.
WITH Selection
AS (
-- The Product History records to summarize
-- Columns converted to SQL_VARIANT for a later UNPIVOT operation,
-- which requires all inputs to have the same data type
SELECT ProductHistory,
ProductId = CONVERT(SQL_VARIANT, ProductId),
ProductDescription = CONVERT(SQL_VARIANT, ProductDescription),
EngineeringOnly = CONVERT(SQL_VARIANT, EngineeringOnly),
CreatedUser = CONVERT(SQL_VARIANT, CreatedUser),
CreatedDate = CONVERT(SQL_VARIANT, CreatedDate),
LastModifiedUser = CONVERT(SQL_VARIANT, LastModifiedUser),
LastModifiedDate = CONVERT(SQL_VARIANT, LastModifiedDate)
FROM ProductsHistory
WHERE Product = 55085
),
KeyValuePairs
AS (
-- UNPIVOT each history row into key-value pairs
-- e.g. EngineeringOnly = 0 becomes two columns
-- Name = 'EngineeringOnly'; Value = 0;
SELECT KeyValuePairs.ProductHistory,
KeyValuePairs.Name,
KeyValuePairs.Value,
KeyValuePairs.LastModifiedUser,
KeyValuePairs.LastModifiedDate
FROM Selection
UNPIVOT (
Value
FOR Name IN
(
-- List of columns to produce audit trail for
ProductId,
ProductDescription,
EngineeringOnly
--CreatedUser,
--CreatedDate
--LastModifiedUser,
--LastModifiedDate
)
) KeyValuePairs
),
ChangeList
AS (
-- The previous step generates duplicates for each history
-- row where a given value did not change (but something else did).
-- This CTE selects just the most recent example of each combination
-- of Name and Value. We also assign a change_sequence number here,
-- and count the number of changes for each key name.
SELECT change_sequence =
ROW_NUMBER()
OVER (
PARTITION BY KVP.Name
ORDER BY KVP.ProductHistory DESC),
records =
COUNT(*)
OVER (
PARTITION BY KVP.Name),
KVP.Name,
KVP.Value,
KVP.ProductHistory,
KVP.LastModifiedUser,
KVP.LastModifiedDate
FROM KeyValuePairs KVP
WHERE ProductHistory =
(
SELECT MAX(KVP2.ProductHistory)
FROM KeyValuePairs KVP2
WHERE KVP2.Name = KVP.Name
AND KVP2.Value = KVP.Value
)
)
SELECT CL.Name, -- Name of the column that changed
CL.change_sequence, -- Sequence number of the change
CL.Value, -- New value
CL.ProductHistory, -- Primary Key from the audit table
CL.LastModifiedUser,
CL.LastModifiedDate
FROM ChangeList CL
-- There must be at least two records
-- for any change in value.
WHERE CL.records > 1
ORDER BY
CL.Name,
CL.change_sequence;
Output:
Name change_sequence Value ProductHistory LastModifiedUser LastModifiedDate
EngineeringOnly 1 1 20982 Steve_Smith 2010-02-22 12:44:00.000
EngineeringOnly 2 0 19316 xxxxx 2010-01-25 10:36:00.000
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply