Hello All,
Please help on my below task.
Table Script added as attached document,as it have restrictions about limited character length to post here
Source Table:
Please check in script,I could not copied over here
Target Table:
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+
| IndividualKey | IndividualID | AgencyKey | AgencyID | EpisodeRelationshipKey | EpisodeRelationshipCode | EpisodeStartDateKey | EpisodeStartDate | edinum | EpisodeRequestDate |
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20131002 | 2013-10-02 | 1100776705 | 1800-01-01 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20140409 | 2014-04-09 | 1100919919 | 1800-01-01 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20150130 | 2015-01-30 | 1101184492 | 1800-01-01 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20140409 | 2014-04-08 | 1100919919 | 1800-01-01 |
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+


Output/Expected Result:
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+
| IndividualKey | IndividualID | AgencyKey | AgencyID | EpisodeRelationshipKey | EpisodeRelationshipCode | EpisodeStartDateKey | EpisodeStartDate | edinum | EpisodeRequestDate |
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20131002 | 2013-10-02 | 1100776705 | 1800-01-01 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20140409 | 2014-04-09 | 1100919919 | 2014-04-09 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20150130 | 2015-01-30 | 1101184492 | 2015-01-22 |
| 2534506 | 001113 | 199 | 054 | 27 | 2 | 20140409 | 2014-04-08 | 1100919919 | 1800-01-01 |
+---------------+--------------+-----------+----------+------------------------+-------------------------+---------------------+------------------+------------+--------------------+
Note:Please notice that 'EpisodeRequestDate' column updated on Output table/expected result


Conditions:A
1)source individualid=target Individualid and source agencyid=target agencyid and
2)source EpisodeRelationshipCode='5' and target EpisodeRelationshipCode='2'
3)and source EpisodeRequestDate<= target EpisodeStartDate and source EpisodeRequestDate-target Episodestartdate <=30 days
4)after above conditions
[if found single qualified records on target for one single source record,then update on target EpisodeRequestDate with source EpisodeRequestDate]
[if found multiple qualified records on target for one single source record,then select max of target record and update on target EpisodeRequestDate with source EpisodeRequestDate]
[if found multiple qualified records on source for one single target record,then select max of source record and update on target EpisodeRequestDate with source EpisodeRequestDate]
[if found multiple qualified records on source for multiple qualified target records,then select max of source and target record and update on target EpisodeRequestDate with
source EpisodeRequestDate]
Conditions:B
All above conditions not qualified ,then insert source records into target
How to identify ,which of the source record will be reach to update/insert
please check attached documents for easy understand.

Click image for larger version. 

Name:	Input_Output.png 
Views:	1 
Size:	37.2 KB 
ID:	16690Table_Script.txt