Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
4.56/5 (2 votes)
Hi Guys,

I need some help, I need to compare using my audit log on which column has changed and its old and new data. So I should have Changed_Column, Changed_From, Changed_To
CREATE TABLE #Sample_Ext_Upl_Hdr_HIST (
	[recid] [varchar](50) NULL,
	[upld_type] [varchar](50) NULL,
	[batch_id] [varchar](50) NULL,
	[season_id] [varchar](50) NULL,
	[T0] [varchar](50) NULL,
	[BU_Team] [varchar](50) NULL,
	[Dev_Team] [varchar](50) NULL,
	[remarks] [nvarchar](200) NULL,
	[created_by] [varchar](50) NULL,
	[create_dt] [datetime] NULL,
	[updated_by] [varchar](50) NULL,
	[update_dt] [datetime] NULL,
	[Audit_Type] [varchar](50) NULL,
	[Audit_Dt] [datetime] NULL
)
GO
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2019-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-29T17:31:17.613' AS DateTime), N'UPDATE-OLD', CAST(N'2018-08-30T01:02:32.203' AS DateTime))
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2018-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-30T01:02:01.980' AS DateTime), N'UPDATE-NEW', CAST(N'2018-08-30T01:02:32.203' AS DateTime))


Output Should be:

upld_type batch_id	season_id  T0	BU_Team	changed_column	changed_from	changed_to
Sample Style Header	SMP-81520182	2018-SS	ADI	BAS	season_id	2019-SS	2018-SS


Please help me how to..

What I have tried:

This is what I have tried so far.. (only 2 columns checking yet..) But I need to achieve the columns :
Changed_Column, Changed_From, Changed_To

SQL
SELECT 
   T1.RECID,
   T1.BATCH_ID,
   CASE 
     WHEN T1.SEASON_ID = T2.SEASON_ID 
     THEN T1.SEASON_ID 
     ELSE 'OLD:' + CAST(T1.SEASON_ID AS VARCHAR(20)) + ', NEW:' + CAST(T2.SEASON_ID AS VARCHAR(20)) 
     END AS SEASON_ID,
   CASE 
     WHEN T1.BU_TEAM = T2.BU_TEAM 
     THEN T1.BU_TEAM
     ELSE 'OLD:' + CAST(T1.BU_TEAM AS VARCHAR(20)) + ', NEW:' + CAST(T2.BU_TEAM AS VARCHAR(20)) 
     END AS BU_TEAM
FROM #SAMPLE_EXT_UPL_HDR_HIST T1
INNER JOIN #SAMPLE_EXT_UPL_HDR_HIST T2
   ON T1.RECID=T2.RECID 
   AND T1.BATCH_ID=T2.BATCH_ID 
   AND T1.AUDIT_TYPE='UPDATE-OLD' 
   AND T2.AUDIT_TYPE='UPDATE-NEW'
WHERE   
(
   T1.SEASON_ID <> T2.SEASON_ID 
   OR T1.BU_TEAM <> T2.BU_TEAM 
)
Posted
Updated 4-Sep-18 23:56pm
v3
Comments
Santosh kumar Pithani 31-Aug-18 6:25am    
Hi, why your inserting and retrieving from same table ;using any triggers?
berrymaria 31-Aug-18 6:50am    
Yes, I used triggers.. that is why it is from same table..
Santosh kumar Pithani 31-Aug-18 7:54am    
i just asked,check my solution and let me know soon.

SQL
SELECT DISTINCT 
  [upld_type],
  [batch_id],
  [T0],
  [BU_Team],
  (SELECT TOP(1) [season_id] FROM #Sample_Ext_Upl_Hdr_HIST AS s1 
      where s.[create_dt]=s1.[create_dt] AND s1.AUDIT_TYPE='UPDATE-OLD'
             ORDER BY [update_dt]) AS changed_from,
  (SELECT TOP(1) [season_id] FROM #Sample_Ext_Upl_Hdr_HIST AS s2
      where s.[create_dt]=s2.[create_dt] AND s2.AUDIT_TYPE='UPDATE-new'
             ORDER BY [update_dt]) AS changed_to	 
 from #Sample_Ext_Upl_Hdr_HIST  AS s
 
Share this answer
 
v2
Comments
berrymaria 31-Aug-18 20:20pm    
Hi Santosh, it will be a problem if I'll use the create_dt in where clause, since in audit logs, it can may be multiple, not just the given (2 records only). I also need to display on what column has changed. Thank you.
Santosh kumar Pithani 3-Sep-18 23:59pm    
Hi,there is no problem with creation_date column because of its millisecond time. sorry for late response..
berrymaria 4-Sep-18 3:18am    
Hmmm how about the viewing to what column has been updated?
Santosh kumar Pithani 4-Sep-18 4:15am    
you have using DDL Trigger on auditlog table so complete information in table try to get what you need if you need any kind of query help ?
So what I did was manipulate the Audit Trigger, created new table for my history.. and hard coded the columns if there's an update.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900