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!
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.. ;)
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
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.