March 22, 2013 at 9:46 am
Hello
Need help to solve one problem that I somehow caused...
I have a table that has nearly 100 columns, and i have a "copy" of this table with 2 extra columns, to maintain a kind of history. This History table it's filled with the help of a trigger in the main table, that adds a copy of the original row before the change, plus the kind of change made (delete,update), and the date of the change.
Now i'm trying to get the history for one particular ID, the PK for the main table.
How can I, create a temp table, with every row of the History table, but in each line only show differences from the previous line.
For example:
I have a table with columns from A to Z, and the original values for the rows were a to z.
The first row in the History table, has all the values, but the column A changed from a to 10.
The second row, the column T changed to 99...
The result that i'm trying to get is something like:
Row1 only the 10 in the A column, and the other columns set to null
Row2 only the 99 in the T column, and the other columns set to null
...
March 22, 2013 at 9:55 am
Not much details here to provide anything resembling a coded solution but maybe NULLIF would work?
NULLIF(HistoryTable.Col1 = MainTable.Col1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 10:05 am
Mock up something with 10 colums and provide the DDL and some sample data (with inserts) . Show a result set.
We can try to help, but as Sean mentioned, not enough info here
March 22, 2013 at 10:29 am
rootfixxxer (3/22/2013)
Thanks@sean Lange
I know the nullif, but to use that function i need to write every column... :crazy:
@steve-2 Jones - SSC Editor
I'll create some sample data... The original table it's a little big 🙂
Honestly there really is no way around coding all the column names for something like this but that certainly doesn't mean you have to manually type them all. You can use sys.columns to help you build your sql code. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 10:49 am
GO
IF OBJECT_ID('TempDB..#OrginalTable','U') IS NOT NULL
DROP TABLE #OrginalTable
IF OBJECT_ID('TempDB..#HistoryTable','U') IS NOT NULL
DROP TABLE #HistoryTable
CREATE TABLE #OrginalTable(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
A CHAR(1),
B CHAR(1),
C CHAR(1),
D CHAR(1),
E CHAR(1)
)
CREATE TABLE #HistoryTable(
Change NVARCHAR(10),
DateValue SMALLDATETIME,
ID INT,
A CHAR(1),
B CHAR(1),
C CHAR(1),
D CHAR(1),
E CHAR(1),
PRIMARY KEY (DateValue,ID)
)
--- SOME SAMPLE DATA FINAL STATE
INSERT INTO #OrginalTable
SELECT '1','b','c','R','e' UNION ALL
SELECT '0','Y','W','Z','9'
--- SAMPLE TRIGGER DATA
INSERT INTO #HistoryTable
SELECT 'UPDATE','03/20/2013',1,'a','b','c','d','e' UNION ALL -- Row 1 - Orignal
SELECT 'UPDATE','03/21/2013',1,'1','b','c','d','e' UNION ALL -- Row 1 - After 1st change
SELECT 'UPDATE','03/20/2013',2,'X','Y','W','Z','Z' UNION ALL -- Row 2 - Orignal
SELECT 'UPDATE','03/21/2013',2,'0','Y','W','Z','Z' -- Row 2 - After 1st change
*EDITED* - Something similar to this, but only the first line complete, and the other lines, only show the values that are different from the previous line... In this sample the second row, only the "visible" the d value, in the third row the a value...
SELECT [DateValue] = GETDATE(), [Type] = 'Final',A ,B ,C ,D ,E FROM #OrginalTable WHERE ID = 1 UNION ALL
SELECT DateValue , Change , A ,B ,C ,D ,E FROM #HistoryTable WHERE ID = 1 ORDER BY DateValue DESC
Edited, and added the Select in the end of the script.
Thanks
March 22, 2013 at 11:10 am
@sean League
The main problem here it's I'm not seeing how to iteract with each row to compare the values... In the Row N i need to check the values with the row N-1...
The first row, or the latest change of the history tabel compares against the current row in the orignal table, the second row in the history compares against the first row... and so on. If all comparisions were made against the current row...
March 22, 2013 at 11:27 am
Not necessarily..
current row in your audit table should match current row in your live table:
ergo. (assuming you have an incremental identity on your audit table...say you do!) the first row is discarded because there's nothing to compare again and every subsequent row compares itself to the row before it to see what value changed.
kind of like a join of audit.id = audit1.id-1 so you can compare audit.field1 equality with audit1.field1.
Make sense? If equal then null if not then audit1.field.value
March 22, 2013 at 11:46 am
@Erin Ramsay
If i had the Identity in the History table, that was the way to go... but i don't have, when i created this table i set the date and the original id, to be the PK.
But meanwhile i think i found a way to start working in the query, using CTE...
Something like this:
WITH CTE_History AS (
SELECT ROW_NUMBER() OVER(ORDER BY DateValue DESC) AS RowNum,*
FROM #HistoryTable
WHERE ID = 1
)
Now i just have to use the sys columns to build the query... :unsure:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply