How to get the data based on another table's data

[dandanyang]

In table 1, data return as:
StartDate         EndDate               Value   
01/01/2001    12/30/2005          a
01/01/2006    12/30/2012          b
01/01/2013    12/30/2013          c
01/01/2014    12/30/2014          d
........
In table2, there is date field DOL. I need to get query item TargetValue based on:
If DOL fall in the range of StartDate and EndDate in table1, then corresponding TargetValue return. For example:
DOL                      TargetValue
01/02/2001         a
05/03/2013         c
There is no relationship between table1 and 2.

Please let me know if I am not clear. Thanks!

[cognostechie]

There seems to be a join between both the queries.

Create two query subjects , one for each table and join them with

Table2.DOL >= Table1.StartDate and Table2.DOL <= Table1.EndDate

By the way, is 31st December excluded from calendar in your part of the world ? Just kidding.. ;) 

[dandanyang]

Thanks cognostechie! You are super! Where are my 12/31? They were stolen :)

Continued question:
In table1, there are overlapping period , see below rows of Value as 'a' and 'b'.
StartDate             EndDate            Value         CreateDate
01/01/2001    12/31/2005         a              01/31/2014
01/01/2001    12/31/2012         b              01/31/2004
01/01/2013        12/31/2013          c             ....
01/01/2014        12/31/2014          d            ....

If DOL fall in the overlapping range of StartDate and EndDate in table1 ( from 01/01/2001 to 12/30/2005 ) and two Value return ('a' and 'b'),  then choose Value with greatest CreatedDate. For example:
DOL                      TargetValue
01/02/2001         a (DOL fall into overlapping period, and Value could be 'a' or 'b'. Since Value 'a' has greatest CreateDate, then TargetValue is 'a')
01/02/2006         b

[cognostechie]

You did not mention this complicacy earlier, did you? Anyway, this is what you can do.

1> Create a query subject with all fields from Table 1.
2> Create another field using MAX function so that it picks up 01/31/2014 for the example provided by you. You might need to provide a for clause (group by clause) for the MAX function -- that should be StartDate.
3> Join this to your original query subject with StartDate = StartDate and CreateDate = CreateDate. This will eliminate
     all records with older CreateDates so you will get only the latest one.
4> Continue with the join strategy I mentioned earlier and it would join only to the most recent record

What we are doing here is to simply eliminate the earlier records from Table1 wherever there are overlapping period. You may also
want to check with your functional team if there can be another scenario where you can find duplicate records so that you cover all the overlapping scenarios. Table1 seems to be like a table storing history values and I think there would be other fields in this table apart from what you mentioned.