February 4, 2011 at 11:56 am
Hello all;
I have given the task to produce a report off audit table, i am stuck over here as I am going to gear up for database after spending about 6-8 months working purely on web development, so I seek some push here.
Following are the main columns in addition to user_ID and change_date (all nvarchar)
User Date OrderID Address1 Travel Mode RecordType
sa 4 Feb 001 Home Self U-Old
sa 4 Feb 001 Work Self u_new
sa 4 Feb 001 Work Self u_Old
sa 4 Feb 001 Shop Bus u_New
U-old stands for previous value, u_new stands for the new value
my report should look like this
UserID Date Change Field Name OldValue NewValue
sa 4 Feb Address1 Home Work
sas 4 Feb TravelMode self self
sa 4 Feb Address1 Work shop
sa 4 Feb Travel Mode self bus
I know I need to pivot/unpivot I just cannot put the puzzle together over here.
I look forward to reply, thanks
February 4, 2011 at 12:35 pm
February 4, 2011 at 12:57 pm
Apologies for inconvenience, hope the following gives a clear picture
if OBJECT_ID('tempdb..#TempTable') is not null drop table #TempTable
if OBJECT_ID('tempdb..#Output') is not null drop table #Output
create table #Source
(
UserID nvarchar(10)
,ChangeDate datetime
,OrderID nvarchar(20)
,Address1 nvarchar(100)
,TravelMode nvarchar(100)
,RecordType nvarchar(10)
)
insert into #Source
select
'sa', GETDATE(), '001', 'Home', 'Self', 'u-old'
union all
select
'sa', GETDATE(), '001', 'work', 'Self', 'u-new'
union all
select
'sa', GETDATE(), '001', 'work', 'Self', 'u-old'
union all
select
'sa', GETDATE(), '001', 'shop', 'Bus', 'u-new'
select * from #Source
-- desired output
create table #output
(
UserID nvarchar(100)
,ChangeDate datetime
,FieldName nvarchar(100)
,OldValue nvarchar(100)
,NewValue nvarchar(100)
)
insert into #output
select 'sa', GETDATE(), 'OrderID', '001', '001'
Union all
select 'sa', GETDATE(), 'Address1', 'Home', 'work'
Union all
select 'sa', GETDATE(), 'OrderID', '001', '001'
Union all
select 'sa', GETDATE(), 'TravelMode', 'self', 'bus'
Union all
select 'sa', GETDATE(), 'Address1', 'Work', 'Shop'
select * from #output
drop table #Source
drop table #output
Regards,
Kazim
February 4, 2011 at 1:17 pm
At first glance, this doesn't seem like just a simple unpivot. I'm assuming this is just an issue in your sample data, but you don't have any field in this table specifying the order of values. ie. What makes the audit home -> work instead of home -> shop?
February 4, 2011 at 1:26 pm
Also, why is OrderID present in this "audit" table without a change where as the other two are only present when there's a change? Is there an actual need for that or is that just bad sample data?
Are you recording it once before each line that changes and then listing the differences each on their own rows below it intentionally or is that random?
February 4, 2011 at 1:27 pm
home -> work instead of home -> shop means when the record inserted, it carried the value home, then it was updated to work and then updated to shop. Means, it was modified twice
Another point, whenever a record is being edited and saved, value for each column is being saved. This means some columns may or may not have new value.
I agree, it won't be that straight forward and therefore I am planning to do it in chunks.
I am currently through with UnPivot, the straightforward one. next is to display new and old value both in one row. I am ignoring the scenario of multi edits right now, for now, a record that has been modified twice (with 4 rows) will be displayed twice.
Regards,
Kazim
February 4, 2011 at 1:34 pm
kazim.raza (2/4/2011)
home -> work instead of home -> shop means when the record inserted, it carried the value home, then it was updated to work and then updated to shop. Means, it was modified twice
Right, I get that... my point is that your sample data just has GETDATE() all the way down the line so the changedate on all the rows is the same; ie. I don't know if it went from home -> shop -> work or home -> shop -> work or work -> home -> work -> shop for that matter. I just wanted to verify that your real data had different dates and that they are what is being used to determine the order.
Not sure if you saw it or not, but I posted a second question while you were answering my first, so please see above.
February 4, 2011 at 2:47 pm
apologies for the delayed response, I was on may to home.
You are right, OrderID should not be there, since it is constant. Actually, whatever hasn't changed should not be there.
GetDate() holds the same value as its sample
Regards,
Kazim Raza
February 7, 2011 at 6:39 am
Here's an example that does what you want. You probably *can* do this with unpivot or outer apply now that you took out the extraneous orderid's, but I didn't have time at the moment to work up an example of that. I'll try to add one later.
You might also need to modify this to rownumber and join via userid in your real dataset.
create table #Source
(
UserID nvarchar(10)
,ChangeDate datetime
,OrderID nvarchar(20)
,Address1 nvarchar(100)
,TravelMode nvarchar(100)
,RecordType nvarchar(10)
)
insert into #Source
select
'sa', '2011-02-04 15:07:29.840', '001', 'Home', 'Self', 'u-old'
union all
select
'sa', '2011-02-04 15:07:36.840', '001', 'work', 'Self', 'u-new'
union all
select
'sa', '2011-02-04 15:07:37.840', '001', 'work', 'Self', 'u-old'
union all
select
'sa', '2011-02-04 15:07:39.840', '001', 'shop', 'Bus', 'u-new'
select * from #Source
create table #output
(
UserID nvarchar(100)
,ChangeDate datetime
,FieldName nvarchar(100)
,OldValue nvarchar(100)
,NewValue nvarchar(100)
)
insert into #output
select 'sa', GETDATE(), 'Address1', 'Home', 'work'
Union all
select 'sa', GETDATE(), 'TravelMode', 'self', 'bus'
Union all
select 'sa', GETDATE(), 'Address1', 'Work', 'Shop'
select * from #output
;WITH R AS (
SELECT *, RN = ROW_NUMBER() OVER (ORDER BY ChangeDate)
FROM #Source)
SELECT R.UserID, R.ChangeDate, 'OrderID' FieldName, R.OrderID OldValue, R2.OrderID NewValue
FROM R
LEFT JOIN R R2 ON R.RN = R2.RN-1
WHERE R.OrderID <> R2.OrderID
UNION ALL
SELECT R.UserID, R.ChangeDate, 'TravelMode' FieldName, R.TravelMode OldValue, R2.TravelMode NewValue
FROM R
LEFT JOIN R R2 ON R.RN = R2.RN-1
WHERE R.TravelMode <> R2.TravelMode
UNION ALL
SELECT R.UserID, R.ChangeDate, 'Address1' FieldName, R.Address1 OldValue, R2.Address1 NewValue
FROM R
LEFT JOIN R R2 ON R.RN = R2.RN-1
WHERE R.Address1 <> R2.Address1
ORDER BY changedate
drop table #Source
drop table #output
February 8, 2011 at 2:45 pm
Hi,
Thanks for the script, I have implemented it this way;
if OBJECT_ID('tempdb..#TempTable') is not null drop table #TempTable if OBJECT_ID('tempdb..#Output') is not null drop table #Output
create table #Source
(
UserID varchar(10)
,ChangeDate datetime
,OrderID varchar(100)
,Address1 varchar(100)
,TravelMode varchar(100)
,RecordType varchar(100)
)
insert into #Source
select
'sa', GETDATE(), '001', 'Home', 'Self', 'u-old'
union all
select
'sa', GETDATE(), '001', 'work', 'Self', 'u-new'
--union all
--select
--'sa', GETDATE(), '001', 'work', 'Self', 'u-old'
--union all
--select
--'sa', GETDATE(), '001', 'shop', 'Bus', 'u-new'
select * from #Source
-- desired output
create table #output
(
UserID nvarchar(100)
,ChangeDate datetime
,FieldName nvarchar(100)
,OldValue nvarchar(100)
,NewValue nvarchar(100)
)
insert into #output
select 'sa', GETDATE(), 'OrderID', '001', '001'
Union all
select 'sa', GETDATE(), 'Address1', 'Home', 'work'
Union all
select 'sa', GETDATE(), 'OrderID', '001', '001'
Union all
select 'sa', GETDATE(), 'TravelMode', 'self', 'bus'
Union all
select 'sa', GETDATE(), 'Address1', 'Work', 'Shop'
--select * from #output
SELECT distinct userid, changedate, fieldname, oldvalue
into #oldValue
FROM
(SELECT userid, changedate, orderid, address1, travelmode
FROM #source where RecordType = 'u-old' ) p
UNPIVOT
(oldvalue FOR fieldname IN
(orderid, address1, travelmode )
)AS unpvt;
GO
SELECT distinct userid, changedate, fieldname, newvalue
into #NewValue
FROM
(SELECT userid, changedate, orderid, address1, travelmode
FROM #source where RecordType = 'u-new' ) p
UNPIVOT
(newvalue FOR fieldname IN
(orderid, address1, travelmode )
)AS unpvt;
GO
select O.userid, O.changedate, O.fieldname, O.oldvalue, N.newvalue
From #OldValue O
Inner Join #NewValue N
on O.userid = N.userid
and O.changedate = N.changedate
and O.fieldname = N.fieldname
select O.userid, O.changedate, O.fieldname, O.oldvalue, N.newvalue
From #OldValue O
Inner Join #NewValue N
on O.userid = N.userid
and O.changedate = N.changedate
and O.fieldname = N.fieldname
and (O.oldvalue <> N.newvalue or O.fieldname = 'orderid')
drop table #OldValue
drop table #Newvalue
drop table #Source
drop table #output
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply