Hi All,
Can some help me with below scenario..
Note: I have asked similar logic few days back. But this is bit different than this.
I have 2 tables
1. Employ table with employ no, country and date(date when he was present in that country).
| Emp_No | Emp_Start_Date | Country |
| 100 | 17/5/2007 | UK |
| 100 | 31/7/2007 | UK |
| 100 | 11/6/2008 | US |
| 100 | 14/1/2010 | AUS |
| 100 | 9/7/2011 | AUS |
2. Insider table with employ no, incident number(issues he worked on) and incident_created_date(when that issue was raised).
| Emp_No | Incident Num | Incident_Created_date |
| 100 | 10 | 17/5/2007 |
| 100 | 20 | 17/5/2007 |
| 100 | 30 | 31/7/2007 |
| 100 | 40 | 7/5/2008 |
| 100 | 23 | 11/6/2008 |
| 100 | 32 | 11/8/2008 |
| 100 | 11 | 12/1/2009 |
| 100 | 21 | 4/2/2009 |
| 100 | 31 | 14/1/2010 |
| 100 | 24 | 18/6/2010 |
| 100 | 53 | 9/7/2010 |
| 100 | 65 | 10/3/2011 |
| 100 | 73 | 9/7/2010 |
Now I want to join both the tables and get Country field in Insider table.
Based on Incident_Created_date <= Emp_Start_Date and where ever the dates are missing for that period the country should be previous country
i.e. from above employ table we don't have any details from 12th Jun' 2008 to 13th Jan' 2010.
| 100 | 11/6/2008 | US |
| 100 | 14/1/2010 | AUS |
But we will have data for this period in Insider table and for those records I want country to be US.
Like same for 1st Aug'2007 to 10th Jun'2008 Country = UK
| 100 | 31/7/2007 | UK |
| 100 | 11/6/2008 | US |
Expected Output:
| Emp_No | Incident Num | Incident_Created_date | Country |
| 100 | 10 | 17/5/2007 | UK |
| 100 | 20 | 17/5/2007 | UK |
| 100 | 30 | 31/7/2007 | Uk |
| 100 | 40 | 7/5/2008 | UK |
| 100 | 23 | 11/6/2008 | US |
| 100 | 32 | 11/8/2008 | US |
| 100 | 11 | 12/1/2009 | US |
| 100 | 21 | 4/2/2009 | US |
| 100 | 31 | 14/1/2010 | AUS |
| 100 | 24 | 18/6/2010 | AUS |
| 100 | 53 | 9/7/2010 | AUS |
| 100 | 65 | 10/3/2011 | AUS |
| 100 | 73 | 9/7/2010 | AUS |
Thanks in Advance..
