Hi All,
I've below data to create report using PIVOT & case.
--Table Structure
CREATE TABLE #TEMP(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] varchar NULL,
[DateTime] [datetime] NULL,
[GPO_Policy] varchar NULL,
CONSTRAINT [Ct_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--Table Data
GO
Insert into #Temp (ServerName,DateTime,GPO_Policy)
Select 'Server_X',GetDate()-24,' OU Policy' Union All
Select 'Server_X',GetDate()-24,' CPS_PowerShell_ExecPolicy_RemoteSigned_OverRide' Union
Select 'Server_X',GetDate()-24,' CPS_Firew_Disable_OverRide' Union
Select 'Server_X',GetDate()-24,' CPS_UAC_Disable_OverRide' Union
Select 'Server_X',GetDate()-24,' CPS_CORP_SQL_2008_2012_2014' Union
Select 'Server_X',GetDate()-24,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide' Union
Select 'Server_X',GetDate()-24,' CPS_2012R2_RDS_Policy' Union
Select 'Server_X',GetDate()-24,' CPS_2012_R2_BASE_OS' Union
Select 'Server_X',GetDate()-24,' CPS_WSUS_Config' uNION
Select 'Server_Y',GetDate()-24,' CPS_CORP_SQL_2008_2012_2014' Union
Select 'Server_Y',GetDate()-24,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide' Union
Select 'Server_Y',GetDate()-24,' CPS_2012R2_RDS_Policy' Union
Select 'Server_Y',GetDate()-24,' CPS_2012_R2_BASE_OS' Union
Select 'Server_Y',GetDate()-24,' CPS_WSUS_Config' union
Select 'Server_X',GetDate()-14,' OU Policy' Union All
Select 'Server_X',GetDate()-14,' CPS_PowerShell_ExecPolicy_RemoteSigned_OverRide' Union
Select 'Server_X',GetDate()-14,' CPS_Firew_Disable_OverRide' Union
Select 'Server_X',GetDate()-14,' CPS_UAC_Disable_OverRide' Union
Select 'Server_X',GetDate()-14,' CPS_CORP_SQL_2008_2012_2014' Union
Select 'Server_X',GetDate()-14,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide' Union
Select 'Server_X',GetDate()-14,' CPS_2012R2_RDS_Policy' Union
Select 'Server_X',GetDate()-14,' CPS_2012_R2_BASE_OS' Union
Select 'Server_X',GetDate()-14,' CPS_WSUS_Config' uNION
Select 'Server_Y',GetDate()-14,' CPS_CORP_SQL_2008_2012_2014' Union
Select 'Server_Y',GetDate()-14,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide' Union
Select 'Server_Y',GetDate()-14,' CPS_2012R2_RDS_Policy' Union
Select 'Server_Y',GetDate()-14,' CPS_2012_R2_BASE_OS' Union
Select 'Server_Y',GetDate()-14,' CPS_WSUS_Config' union
Select 'Server_X',GetDate()-4,' OU Policy' Union All
Select 'Server_X',GetDate()-4,' CPS_PowerShell_ExecPolicy_RemoteSigned_OverRide' Union
Select 'Server_X',GetDate()-4,' CPS_Firew_Disable_OverRide' Union
Select 'Server_X',GetDate()-4,' CPS_UAC_Disable_OverRide' Union
Select 'Server_X',GetDate()-4,' CPS_CORP_SQL_2008_2012_2014_2016' Union -- Old data modified
Select 'Server_X',GetDate()-4,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide_Test' Union --Old Data modified
Select 'Server_X',GetDate()-4,' CPS_2012R2_2014_RDS_Policy' Union --Old Data modified
Select 'Server_X',GetDate()-4,' CPS_2012_R2_BASE_OS' Union
Select 'Server_X',GetDate()-4,' CPS_WSUS_Config' Union
Select 'Server_X',GetDate()-4,' C-ENT Domain Logon Warning Banner' union --New Value added
Select 'Server_Y',GetDate()-4,' CPS_CORP_SQL_2008_2012_2014' Union
Select 'Server_Y',GetDate()-4,' CPS_RDS_Idle_Session_TimeOut_Disabled_OverRide' Union
Select 'Server_Y',GetDate()-4,' CPS_2012R2_RDS_Policy' Union
Select 'Server_Y',GetDate()-4,' CPS_WSUS_Config' Union
Select 'Server_Z',GetDate()-4,' CPS_WSUS_Config' --New Server Added
-- I need output in below format.
Please find few piece of script I'm using
--To Find modified Data
Select ServerName,GPO_Policy from #Temp where GPO_Policy in
(Select GPO_Policy from #Temp where convert(date, DateTime, 109)=
(SELECT TOP 1 Convert(Date, DateTime,109) As DateTime FROM #Temp ORDER BY ID DESC)
except
Select GPO_Policy from #Temp where convert(date, DateTime, 109)=
(Select TOP 1 Convert(Date, DateTime,109) As DateTime from #Temp where convert(date, DateTime, 109)
Not in (SELECT TOP 1 Convert(Date, DateTime,109) As DateTime FROM #Temp ORDER BY ID DESC) Order by ID desc))
--To Find New server is added
Select ServerName,GPO_Policy from #Temp where ServerName in(
Select Distinct ServerName From #Temp Where convert(date, DateTime, 109)=
(SELECT TOP 1 Convert(Date, DateTime,109) As DateTime FROM #Temp ORDER BY ID DESC)
Except
Select Distinct ServerName from #Temp where convert(date, DateTime, 109)=
(Select TOP 1 Convert(Date, DateTime,109) As DateTime from #Temp where convert(date, DateTime, 109)
Not in (SELECT TOP 1 Convert(Date, DateTime,109) As DateTime FROM #Temp ORDER BY ID DESC) Order by ID desc))
--I'm not a t-SQL expert, I've created basic PIVOT structure.
Select * from
(
Select GPO.ServerName,cast(Convert(date, GPO.DateTime,109) as Varchar(15)) as GDate, GPO.GPO_Policy from #Temp as GPO
) AS T_GPO
PIVOT
(
Max(GPO_Policy)
--Max(T_GPO.ID)
FOR GDate IN ([2016-01-22],[2016-01-27])
)piv