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'
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

SELECT A.AuditDate,A.Code,A.Auditor,A.EventID,T.EditDate,T.Username
	(SELECT MAX(A.AuditDate) As AuditDate, B.EditDate, B.Username, B.Code
		TableB B
			ON A.code=B.code AND
				A.AuditDate BETWEEN B.EditDATE
								WHERE B.code=code AND EditDate>B.EditDate)
	GROUP BY  B.EditDate, B.Username, B.Code
	) T
	LEFT JOIN TableA A ON T.code=A.code AND T.AuditDate=A.AuditDate

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael-ThomasAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.