How to update on target table with source table

Question

Post reply

How to update on target table with source table

tibco29

SSChasing Mays

Points: 655
More actions
December 31, 2015 at 3:40 am

#323347

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](65) NULL,[AgencyID] [char](5) NULL,[AgencyKey] [int] NULL,[EpisodeRelationshipCode] [char](4) NULL,[EpisodeStartDate] [date] NULL,[EpisodeEndDate] [date] NULL,[EpisodeRequestDate] [date] NULL,[edinum] [nvarchar](80) 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](65) NULL,
[AgencyKey] [int] NOT NULL,
[AgencyID] [char](5) NULL,
[EpisodeRelationshipKey] [int] NOT NULL,
[EpisodeRelationshipCode] [char](4) NULL,
[EpisodeStartDateKey] [int] NOT NULL,
[EpisodeStartDate] [date] NULL,
[edinum] [varchar](50) 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

tibco29 (12/31/2015)
Please find attached document,will let to know about Input and output as well

please read the following document and then post back with DDL/sample data and expected results.

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Hi

Thanks for scripts.

Please see code below, where I have added an IDENTITY column to each table to help determine which rows are which.

I think I need some more explanation on your criteria please.

If you run the code below, I "think" according to my interpretation of your "rules" that the source row ID 4 should update target row 1?.....but I have probably misunderstood!

where there are multiple matches you repeatedly use the word "max" record....what column are you using to determine "max".

I also think it would be good to get sample data in source that doesn't match target and also that make sure that you provide sufficent sample data to cover all permutations (include specific data in each table that you know will not create a match.)

USE [tempdb]

GO

CREATE TABLE [dbo].[Source]

(ID INT IDENTITY(1, 1) NOT NULL,

[IndividualKey] [INT] NULL,

[IndividualID] [VARCHAR](65) NULL,

[AgencyID] [CHAR](5) NULL,

[AgencyKey] [INT] NULL,

[EpisodeRelationshipCode] [CHAR](4) NULL,

[EpisodeStartDate] [DATE] NULL,

[EpisodeEndDate] [DATE] NULL,

[EpisodeRequestDate] [DATE] NULL,

[edinum] [NVARCHAR](80) 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]

(ID INT IDENTITY(1, 1) NOT NULL,

[IndividualKey] [INT] NOT NULL,

[IndividualID] [VARCHAR](65) NULL,

[AgencyKey] [INT] NOT NULL,

[AgencyID] [CHAR](5) NULL,

[EpisodeRelationshipKey] [INT] NOT NULL,

[EpisodeRelationshipCode] [CHAR](4) NULL,

[EpisodeStartDateKey] [INT] NOT NULL,

[EpisodeStartDate] [DATE] NULL,

[edinum] [VARCHAR](50) 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))

SELECT s.ID AS s_ID,

s.IndividualID AS s_IID,

s.AgencyID AS s_AID,

s.EpisodeRelationshipCode AS s_ERC,

s.EpisodeStartDate AS s_ESD,

s.EpisodeRequestDate AS s_ERD,

t.ID AS t_ID,

t.IndividualID AS t_IID,

t.AgencyID AS t_AID,

t.EpisodeRelationshipCode AS t_ERC,

t.EpisodeStartDate AS t_ESD,

t.EpisodeRequestDate AS t_ERD,

DATEDIFF(day, s.EpisodeRequestDate, t.EpisodeStartDate) AS daydiff,

t.edinum

FROM Source AS s

LEFT OUTER JOIN target AS t ON s.IndividualID = t.IndividualID

AND s.AgencyID = t.AgencyID

AND s.EpisodeRequestDate <= t.EpisodeStartDate

WHERE(s.EpisodeRelationshipCode = '5')

AND (t.EpisodeRelationshipCode = '2')

AND (DATEDIFF(day, s.EpisodeRequestDate, t.EpisodeStartDate) <= 30)

ORDER BY s_ID,

t_ID;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

tibco29 SSChasing Mays Points: 655 More actions · Answer 3

Thanks for quick response.

Please have a look on below details

-->source row ID 4 should update target row 1?

Ans) Yes,you are absolutly right(source '2013-09-25'<='2013-10-02') and datedifference is also <=30 days.This is one to one match

-->I also think it would be good to get sample data in source that doesn't match target and also that make sure that you provide sufficent sample data

Ans)Please run below insert statement on source table,this is the record where not going to qualifying under all conditions and finally will move to target table as a new row.

INSERT [dbo].[Source] ([ID], [IndividualKey], [IndividualID], [AgencyID], [AgencyKey], [EpisodeRelationshipCode], [EpisodeStartDate], [EpisodeEndDate], [EpisodeRequestDate], [edinum]) VALUES (5, 2534506, N'001113', N'054 ', 199, N'5 ', CAST(0xAF060A00 AS Date), CAST(0xA5370B00 AS Date), CAST(0xDB3A0B00 AS Date), N'1001112345')

-->what column are you using to determine "max"?

Ans)

If found multiple qualified records at source table,then max(EpisodeRequestDate,EpisodeEndDate)

If found multiple qualified records at target table,then max(EpisodeStartDate)

Note: Main objective is find exactly 1 record of source and update exactly corresponding 1 record on target

Example:

2 of source records(Record1,Record2) qualified on 1 target record(Record3),then max of 2 source records(Record2) will become 1 row and that 1 row will update on target row(Record3)

tibco29 SSChasing Mays Points: 655 More actions · Answer 4

Hello All,

I got solution for my question, special thanks to SSCrazy, who has given right solution path for my task.I am posting final solution query, for helping to some other guys.

drop table Factepisodesourcearel5targetarel2

select *

into Factepisodesourcearel5targetarel2

from (

select IndividualID,agencyid,EpisodeRelationshipCode

--,Episodestartdate

,EpisodeRequestDate

--,days

,edinum,

sourceepisodestartdate as EpisodeStartDate,

CASE WHEN datediff(dd, EpisodeRequestDate , Episodestartdate)*-1<=30 and rn2=1 and rn3=1

THEN 'Update'

ELSE 'Insert'

END AS filter

,

CASE WHEN datediff(day, EpisodeRequestDate , Episodestartdate)*-1<=30 and rn2=1 and rn3=1

THEN guid123

ELSE edinum

END AS[update/insertedinum]

from(

select p.pn_ as IndividualID,p.org as agencyid,p.edinum,p.arel as EpisodeRelationshipCode ,ps.Episodestartdate,p.EpisodeRequestDate,

datediff(day, p.EpisodeRequestDate , ps.Episodestartdate) days,

p.episodestartdate as sourceepisodestartdate,

rank() OVER(PARTITION BY p.pn_,p.org,ps.Episodestartdate

ORDER BY datediff(day, p.EpisodeRequestDate , ps.Episodestartdate) ,p.episoderequestdate,p.episodeenddate ,p.edinum)rn2

,rank() OVER(PARTITION BY ps.IndividualID ,ps.[AgencyID],ps.Episodestartdate

ORDER BY datediff(day, p.EpisodeRequestDate , ps.Episodestartdate) ,ps.Episodestartdate , ps.edinum)rn3

,CASE WHEN datediff(day, p.EpisodeRequestDate , ps.Episodestartdate)*-1 < =30

THEN 'Update'

ELSE 'Insert'

END AS filter

,CASE WHEN datediff(day, p.EpisodeRequestDate , ps.Episodestartdate)*-1< =30

THEN ps.edinum

ELSE p.edinum

END AS guid123

FROM test_outpatientresult p

OUTER APPLY (

SELECT top 1 ps.*

FROM FactOutPatientInformation ps

WHERE ps.IndividualID=p.pn_ and ps.AgencyID=p.org

AND p.EpisodeRequestDate<= ps. EpisodeStartDate

AND ps.EpisodeRelationshipCode = 2

and datediff(day, p.EpisodeRequestDate , ps.Episodestartdate)*-1<=30

) AS ps

WHERE p.arel = 5 and source='outpatient'

)a

)b