Get nearest Date in SQL 2000

Posted on 2011-10-10
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 142

    Expert Comment

    by: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

    LVL 22

    Accepted Solution

    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
    		LEFT JOIN TableA A
    			ON A.code=B.code AND
    				A.AuditDate BETWEEN B.EditDATE
    					AND ISNULL((SELECT MIN(EditDate) FROM TableB
    								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

    LVL 2

    Author Closing Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now