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
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
)