CODEWITHSUNDEEP
sqlsql-server
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

select rows from main table based on highest date in child table between a date range

Sorry for the confusing title.

I've this table:

ApplicantID    Applicant Name
-------------------------------
1              Sandeep
2              Thomas
3              Philip
4              Jerin

ALong with this child table which is connected with the above table:

DetailsID      ApplicantID      CourseName        Dt
---------------------------------------------------------------------
1                  1             C1               10/5/2014
2                  1             C2               10/18/2014
3                  1             c3               7/3/2014
4                  2             C1               3/2/2014
5                  2             C2               10/18/2014
6                  2             c3               1/1/2014
7                  3             C1               1/5/2014
8                  3             C2               4/18/2014
9                  3             c3               2/23/2014
10                 4             C1               3/15/2014
11                 4             C2               2/20/2014
12                 4             C2               2/20/2014

I want to get applicantsID, for example, when I specify a date range from 4/20/2014 to 3/5/2014 I should have: 

ApplicantID    Applicant Name
-------------------------------
3              Philip
4              Jerin

That means the applicants from the main table that must be in the second table and also the highest date of the second table must fall in the specified date range. Hope the scenario is clear.

Upvotes: 0

Views: 90

Answers (2)

radar
radar

Reputation: 13425

you can use window analytic function row_number to get applicant with maximum date in the given time range.

select T1.[ApplicantID], [Applicant Name]
from Table1 T1
join ( select [ApplicantID], 
              ROW_NUMBER() over ( partition by [ApplicantID] order by Dt desc) as rn
       from Table2
       where Dt  BETWEEN '3/5/2014' AND '4/20/2014'
      ) T
on T1.[ApplicantID] = T.[ApplicantID]
and T.rn =1

Upvotes: 3

Siyual
Siyual

Reputation: 16917

You will need to pull the MAX per ApplicantId with a GROUP BY in a sub-query, then JOIN to that result. This should work for you:

Select  A.ApplicantId, A.[Applicant Name]
From    ApplicantTableName  A
Join
(
    Select  D.ApplicantId, Max(D.Dt) DT
    From    DetailsTableName    D
    Group By D.ApplicantId
) B On A.ApplicantId = B.ApplicantId
Where   B.DT Between '03/05/2014' And '04/20/2014'

Upvotes: 1

Related Questions