Get nearest Date in SQL 2000

Posted on 2011-10-10
Medium Priority
Last Modified: 2012-05-12
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'
Question by:Michael-Thomas
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36947128
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

LVL 22

Accepted Solution

Thomasian earned 2000 total points
ID: 36947222
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


Author Closing Comment

ID: 36959031
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question