SQL Query - Finding earliest date later than..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query - Finding earliest date later than..

Author

Topic

Marco123
Starting Member

6 Posts

Posted - 2010-12-31 : 18:35:48

Hi there,

I am trying to create an SQL query to find the record that corresponds to the earliest date in one table, that is after a certain date in another table.

I have two tables, tblMain and tblApplicants.

In tblMain, there are 2 columns with 20 records. In tblApplicants, there are 3 columns, but over 100 records.

tblMain looks like this:

DateID, Date
1 01/01/2010
2 10/01/2010
3 25/01/2010
4 02/02/2010
etc

tblApplicants looks like this:

FirstName, LastName, DateofApplication
Joe Blogs 16/01/2010
John Smith 04/02/2010
Etc

Basically, for every record in tblApplicants, I am trying to find the earliest date (and corresponding DateID) in tblMain, where the Date is later than the DateofApplication in tblApplicants.

For example, if the DateofApplication is 16/01/2010, the earliest date after this in tblMain would be 25/01/2010 and the corresponding DateID would be 3.

I have attempted to create an SQL query for this as shown below, but I’m not entirely sure how to construct it and, moreover, how to get it work for every single record in tblApplicants.

SELECT tblMain.DateID, tblMain.[Date]
FROM tblApplicants
WHERE tblMain.[Date] = ((SELECT MIN(tblMain.[Date])
FROM tblMain
WHERE tblMain.[Date] > (SELECT DateofApplication
FROM tblApplicants))

Any help would be greatly appreciated.

Thanks,

Marco

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-31 : 18:56:16

You need to identify something from the applicants table to join to as well I presume..this is obviously untested, but you can see the idea. Join Applicants to Main, and also join to a subquery for the smallest date in main. then filter results using the where clause. I might just be a little drunk though, so no guarantees it is even close.

Select a.*, b.DateID, b.[Date]
FROM tblApplications a
inner join tblMain b on a.ID = b.ApplicantID <== you need to join to the applicants table somehow
inner join (Select ApplicantID, min([Date]) as MinDate from tblMain) c
on b.ApplicantID = c.ApplicantID subquery for minimum date, and the link to applicant ID)
Where b.[Date] > c.MinDate

Poor planning on your part does not constitute an emergency on my part.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-31 : 20:46:42

Try one of these:


--better but 2005+
SELECT A.DateofApplication, M.Date, M.DateID
  FROM tblApplicants AS A
       OUTER APPLY
       (SELECT TOP(1) M.DateID, M.Date
          FROM tblMain AS M
         WHERE M.Date > A.DateofApplication
         ORDER BY M.Date) AS M;

--another version
SELECT A.DateofApplication, M.Date, A.DateID
  FROM (SELECT A.DateofApplication,
               (SELECT TOP 1 M.DateID 
                  FROM tblMain AS M
                 WHERE M.Date > A.DateofApplication
                 ORDER BY M.Date) AS DateID
          FROM tblApplicants AS A) AS A
       LEFT OUTER JOIN
       tblMain AS M
       ON M.DateID = A.DateID

Marco123
Starting Member

6 Posts

Posted - 2011-01-02 : 16:16:44

Thanks for this but the problem is there is no common field in order to join the 2 tables. This is because I only need to use one table (tblMain) as a reference for the criteria that would be applied to all records in the other table (tblApplicants). However, I have found out that by using the cartesian join it is possible to get what I’m after. Not sure if it is possible using the inner or outer joins.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources