asked on

Get nearest Date in SQL 2000

I have been trying to get this one to work for a while. I have tried using nearest date examples from the net, but I can't seem to modify any of them to meet my needs. Help would be greatly appreciated.

This is unfortunately for SQL Server 2000 .

I am looking for the most recent matching Audit event where the Audit is after the TableB entry but is also before any other Audit entries with the same code.

TableA contains Audit events for entries in TableB. TableB entries can be Audited more than once.

Example setup

CREATE Table TableA (AuditDate datetime, code varchar(10), Auditor varchar(10), EventID int, IsAudit bit)
CREATE Table TableB (EditDate datetime, Username varchar(10), code varchar(10))

insert into TableB (EditDate, Username, code) values ('7/1/2011', 'Username1', 'CL02')  -- Match 1
insert into TableB (EditDate, Username, code) values ('7/1/2011', 'Username1', 'CL03')  -- Match 2
insert into TableA (AuditDate, code, Auditor, EventID, IsAudit) values ('7/2/2011', 'CL02', 'Auditor1', 1, 1)
insert into TableA (AuditDate, code, Auditor, EventID, IsAudit) values ('7/2/2011', 'CL03', 'Auditor1', 2, 1)  -- Match 2
insert into TableA (AuditDate, code, Auditor, EventID, IsAudit) values ('7/4/2011', 'CL02', 'Auditor2', 3, 1)  -- Match 1
insert into TableB (EditDate, Username, code) values ('7/5/2011', 'Username2', 'CL02')  -- Match 3
insert into TableA (AuditDate, code, Auditor, EventID, IsAudit) values ('7/6/2011', 'CL02', 'Auditor3', 4, 1)  -- Match 3

Open in new window

My desired result set

TableA.AuditDate, TableA.Code, TableA.Auditor, TableA.EventID, TableB.EditDate, TableB.Username
'7/4/2011', 'CL02', 'Auditor2', 3, '7/1/2011', 'User1'
'7/2/2011', 'CL02', 'Auditor1', 3, '7/1/2011', 'User1'
'7/6/2011', 'CL02', 'Auditor3', 3, '7/5/2011', 'User2'

Guy Hengel [angelIII / a3]

something around this should do:

select b.*
 , a1.*
 , a2.*
from tableB b
left join tableA a1
  on a1.Auditor = b.Username 
 and a1.AuditDate = ( SELECT MIN( x1.AuditDate) FROM tableA x1 WHERE x1.Auditor = b.Username AND x1.AuditDate >= b.EditDate )
left join tableA a2
  on a2.Auditor = b.Username 
 and a2.AuditDate = ( SELECT MAX( x2.AuditDate) FROM tableA x2 WHERE x2.Auditor = b.Username AND x2.AuditDate <  b.EditDate )

Open in new window

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael-Thomas

ASKER

Excellent thanks.

I had a bit of an issue with my dev server and the ISNULL portion of the query, but that is because it was running SQL 2000 SP3 :( SP4 fixes the issue.