SQL Query question

Question

SQL Query question

ktlady

SSCommitted

Points: 1509
More actions
November 18, 2015 at 5:15 pm

#309704

Hi all,
I am having trouble to write a sql query to capture history of item so that I could do reporting on it. Hope some SQL expert out there could help me.
Here is table1, or table2, 3 structure which has current value of information:
ItemID: e.g. 123
PN: e.g. PN1
Assigned_to: Tami
Category: Laptop
Here is the Audit log table information:
TableName: e.g. table1 or table2
TableKey: 123
TableField: e.g. Assigned_to
OldValue: Sam
NewValue: Sally
ChangeDate: 2014/1/1
TableName: e.g. table1 or table2
TableKey: 123
TableField: e.g. Assigned_to
OldValue: Sally
NewValue: Tami
ChangeDate: 2015/4/1
With multiple entries of those audit log, I want to have a query to produce the following output:
ItemID,PN, Assign_to, Category, StartDate, EndDate
123, PN1, Sam, Laptop, null, 2014/1/1
123, PN1, Sally, Laptop, 2014/1/01, 2015/4/1
123, PN1, Tami, Laptop, 2015/4/01, 9999/12/31
Thanks so much for any suggestions!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

For future posts, please help us help you. Please read the article at the first link under "Helpful Links" in my signature line at the end of this post. Thanks.

Here's one solution. Details are in the code.

--=====================================================================================================================

-- Create the test tables and data. Nothing in this section is a part of the solution.

-- We're just building test data.

--=====================================================================================================================

--===== If they exist, drop the test tables to make reruns easier in SSMS.

IF OBJECT_ID('tempdb..#Audit' ,'U') IS NOT NULL DROP TABLE #Audit;

IF OBJECT_ID('tempdb..#Table1','U') IS NOT NULL DROP TABLE #Table1;

--===== Create and populate the Audit test table on-the-fly.

SELECT td.TableName, td.TableKey, td.TableField, td.OldValue, td.NewValue, ChangeDate = CAST(td.ChangeDate AS DATE)

INTO #Audit

FROM (

SELECT 'Table1',123,'Assigned_to','Sam' ,'Sally','2014-01-01' UNION ALL

SELECT 'Table1',123,'Assigned_to','Sally','Tami' ,'2015-04-01'

) td (TableName, TableKey, TableField, OldValue, NewValue, ChangeDate)

;

--===== Create and populate the "Table1" test table on-the-fly

SELECT ItemID = 123, PN = 'PN1', Assigned_to = 'Tami', Category = 'Laptop'

INTO #Table1

;

--=====================================================================================================================

-- Here's a solution that uses an "offset self-join"

--=====================================================================================================================

WITH

cteEnumerateAudit AS

( --=== Add a row number by date so we can do an "offset self-join"

-- Since it get's called twice, it might be better to dump it to a Temp Table and do self-join on that.

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY ChangeDate)

,TableKey, OldValue, NewValue, ChangeDate

FROM #Audit

WHERE TableName = 'Table1'

--AND TableKey = '123' --Uncomment if you only want 1 TableKey

AND TableField = 'Assigned_to'

)

,cteOffsetJoin AS

( --=== Do the offset self-join to get the correct data for each Assign_to.

SELECT ItemID = ISNULL(lo.TableKey, hi.TableKey)

,Assign_to = ISNULL(hi.OldValue, lo.NewValue)

,StartDate = lo.ChangeDate

,EndDate = ISNULL(hi.ChangeDate,'99991231')

FROM cteEnumerateAudit lo

FULL JOIN cteEnumerateAudit hi

ON lo.RowNum + 1 = hi.RowNum

)

--===== This joins all the above to the other table for display purposes.

SELECT oj.ItemID, t1.PN, oj.Assign_to, t1.Category, oj.StartDate, oj.EndDate

FROM cteOffsetJoin oj

JOIN #Table1 t1

ON oj.ItemID = t1.ItemID

ORDER BY oj.ItemID, oj.StartDate

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ktlady SSCommitted Points: 1509 More actions · Answer 2

Jeff, you are truly amazing and awesome! Thank you so much for the great help!

As a follow up question: The table2 could have completely different structure with a lot more fields. And for audit log, the TableName, TableKey, TableField could be just about anything, assigned_to value change was just a random example. Is there anyway that I could built cteEnumerateAudit more dynamically and go through each of the field in the table instead of hard-coding?