www.blackholesurfer.com

Code: Select all

SELECT 
rjr.ActivityName, 
rjr.z_Tmp_JobName'Job Name',
a.Instanceid,
b.reconciliationidentity,
DATEADD(s,a.createdate,'19700101')'CI Create Date in Asset',
DATEADD(s,rjr.Create_Date,'19700101')'Job Run Date',
DATEADD(s,rjr.Modified_Date,'19700101')'Job End Date',
a.markasdeleted
      from BMC_Core_BMC_BaseElement a
      join BMC_CORE_BMC_BaseElement b on b.instanceid = a.instanceid 
     Join RE_JOB_RUns rjr on rjr.Instanceid = a.LastREJobRunId
where a.reconciliationidentity in 
                (
                select ReconciliationIdentity from bMC_CORE_BMC_BaseElement 
                where datasetid = 'BMC.ASSET' group by ReconciliationIdentity having count(*) > 1
                )
and a.datasetid = 'BMC.ASSET' and b.datasetid = 'BMC.ASSET'
Order by reconciliationidentity

Code: Select all

With Row_Numbered as 
		(select reconciliationidentity,instanceid,lastscandate, row_number() 
		over (partition by reconciliationidentity order by reconciliationidentity) as Row_Num
		from BMC_CORE_BMC_BaseElement  where datasetid = 'BMC.ASSET' ) 
select a.instanceid'A instanceid','DUPEbyREID' as DUPEbyREID,a.ReconciliationIdentity,DATEADD(s,a.LastScanDate,'19700101')'A scan date', DATEADD(s,r.LastScanDate,'19700101')'R scan date' from Row_Numbered r
join row_numbered a on a.reconciliationidentity = r.reconciliationidentity 
where r.Row_Num > 1
and a.lastscandate < r.lastscandate