Hello All,
Please help on my below task.
Source Table:
+---------------+--------------+----------+-----------+-------------------------+------------------+----------------+--------------------+------------+
| IndividualKey | IndividualID | AgencyID | AgencyKey | EpisodeRelationshipCode | EpisodeStartDate | EpisodeEndDate | EpisodeRequestDate | edinum |
+---------------+--------------+----------+-----------+-------------------------+------------------+----------------+--------------------+------------+
| 2534506 | 001113 | 054 | 199 | 5 | 1800-01-01 | 2015-01-29 | 2015-01-22 | 1101172285 |
| 2534506 | 001113 | 054 | 199 | 5 | 1800-01-01 | 2014-04-09 | 2014-04-09 | 1100917470 |
| 2534506 | 001113 | 054 | 199 | 5 | 1800-01-01 | 2014-04-08 | 2014-03-25 | 1100905953 |
| 2534506 | 001113 | 054 | 199 | 5 | 1800-01-01 | 2013-10-01 | 2013-09-25 | 1100776699 |
+---------------+--------------+----------+-----------+-------------------------+------------------+----------------+--------------------+------------+
+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
Table Script with Schema
CREATE TABLE [dbo].[Source]( [IndividualKey] [int] NULL, [IndividualID] varchar NULL, [AgencyID] char NULL, [AgencyKey] [int] NULL, [EpisodeRelationshipCode] char NULL,[EpisodeStartDate] [date] NULL,[EpisodeEndDate] [date] NULL, [EpisodeRequestDate] [date] NULL, [edinum] nvarchar NULL)
INSERT [dbo].[Source] ([IndividualKey], [IndividualID], [AgencyID], [AgencyKey], [EpisodeRelationshipCode], [EpisodeStartDate], [EpisodeEndDate], [EpisodeRequestDate], [edinum]) VALUES (2534506, N'001113', N'054 ', 199, N'5 ', CAST(0xAF060A00 AS Date), CAST(0x8A390B00 AS Date), CAST(0x83390B00 AS Date), N'1101172285')
INSERT [dbo].[Source] ([IndividualKey], [IndividualID], [AgencyID], [AgencyKey], [EpisodeRelationshipCode], [EpisodeStartDate], [EpisodeEndDate], [EpisodeRequestDate], [edinum]) VALUES (2534506, N'001113', N'054 ', 199, N'5 ', CAST(0xAF060A00 AS Date), CAST(0x63380B00 AS Date), CAST(0x63380B00 AS Date), N'1100917470')
INSERT [dbo].[Source] ([IndividualKey], [IndividualID], [AgencyID], [AgencyKey], [EpisodeRelationshipCode], [EpisodeStartDate], [EpisodeEndDate], [EpisodeRequestDate], [edinum]) VALUES (2534506, N'001113', N'054 ', 199, N'5 ', CAST(0xAF060A00 AS Date), CAST(0x62380B00 AS Date), CAST(0x54380B00 AS Date), N'1100905953')
INSERT [dbo].[Source] ([IndividualKey], [IndividualID], [AgencyID], [AgencyKey], [EpisodeRelationshipCode], [EpisodeStartDate], [EpisodeEndDate], [EpisodeRequestDate], [edinum]) VALUES (2534506, N'001113', N'054 ', 199, N'5 ', CAST(0xAF060A00 AS Date), CAST(0xA5370B00 AS Date), CAST(0x9F370B00 AS Date), N'1100776699')
CREATE TABLE [dbo].[target](
[IndividualKey] [int] NOT NULL,
[IndividualID] varchar NULL,
[AgencyKey] [int] NOT NULL,
[AgencyID] char NULL,
[EpisodeRelationshipKey] [int] NOT NULL,
[EpisodeRelationshipCode] char NULL,
[EpisodeStartDateKey] [int] NOT NULL,
[EpisodeStartDate] [date] NULL,
[edinum] varchar NOT NULL,
[EpisodeRequestDate] [date] NULL
)
INSERT [dbo].[target] ([IndividualKey], [IndividualID], [AgencyKey], [AgencyID], [EpisodeRelationshipKey], [EpisodeRelationshipCode], [EpisodeStartDateKey], [EpisodeStartDate], [edinum], [EpisodeRequestDate]) VALUES (2534506, N'001113', 199, N'054 ', 27, N'2 ', 20131002, CAST(N'2013-10-02' AS Date), N'1100776705', CAST(N'1800-01-01' AS Date))
INSERT [dbo].[target] ([IndividualKey], [IndividualID], [AgencyKey], [AgencyID], [EpisodeRelationshipKey], [EpisodeRelationshipCode], [EpisodeStartDateKey], [EpisodeStartDate], [edinum], [EpisodeRequestDate]) VALUES (2534506, N'001113', 199, N'054 ', 27, N'2 ', 20140409, CAST(N'2014-04-09' AS Date), N'1100919919', CAST(N'1800-01-01' AS Date))
INSERT [dbo].[target] ([IndividualKey], [IndividualID], [AgencyKey], [AgencyID], [EpisodeRelationshipKey], [EpisodeRelationshipCode], [EpisodeStartDateKey], [EpisodeStartDate], [edinum], [EpisodeRequestDate]) VALUES (2534506, N'001113', 199, N'054 ', 27, N'2 ', 20150130, CAST(N'2015-01-30' AS Date), N'1101184492', CAST(N'1800-01-01' AS Date))
INSERT [dbo].[target] ([IndividualKey], [IndividualID], [AgencyKey], [AgencyID], [EpisodeRelationshipKey], [EpisodeRelationshipCode], [EpisodeStartDateKey], [EpisodeStartDate], [edinum], [EpisodeRequestDate]) VALUES (2534506, N'001113', 199, N'054 ', 27, N'2 ', 20140409, CAST(N'2014-04-08' AS Date), N'1100919919', CAST(N'1800-01-01' AS Date))
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