April 6, 2017 at 10:29 pm
Here is my sample data
CREATE TABLE ##Source_table1
(
s_isc VARCHAR(20)
,s_sta VARCHAR(20)
,s_rateDECIMAL
,s_weekdayDATE
,s_starttimeDATETIME
,s_endtimeDATETIME
,s_keyint
)
INSERT INTO ##Source_table1
( s_isc ,
s_sta ,
s_rate ,
s_weekday ,
s_starttime ,
s_endtime,
s_key
)
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123456'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123457'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123458'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123452'
CREATE TABLE ##dest_table2
(
d_isc VARCHAR(20)
,d_sta VARCHAR(20)
,d_rateDECIMAL
,d_dateDATE
,d_timeDATETIME
,d_s_keyINT
,d_idint
)
INSERT INTO ##dest_table2
( d_isc ,
d_sta ,
d_rate ,
d_date ,
d_time,
d_id
)
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-03 10:27:40.000','123'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-03 11:28:25.000','456'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-04 10:27:55.000','789'
/*
SELECT * FROM Source_table1
SELECT * FROM dest_table2
*/
SELECT
S1.s_key,D2.d_id
FROM ##Source_table1 S1
INNER JOIN ##dest_table2 D2 ON
S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)
UPDATE ##dest_table2
SET d_s_key = S1.s_key
FROM ##dest_table2 D2
INNER JOIN ##Source_table1 S1 ON
S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)
/*
DROP TABLE ##dest_table2
DROP TABLE ##Source_table1
*/
If you run above SQL it will create a two temp table. What I want I want to Update "dest_table2.d_s_key" from "source_table1" Because of the linking logic I am using
I am getting a duplicate. That's what I am getting after I run update statement
d_iscd_stad_rated_dated_timed_s_keyd_id
000147349SPR8402017-04-032017-04-03 10:27:40.000123452123
000147349SPR8402017-04-032017-04-03 11:28:25.000123452456
000147349SPR8402017-04-032017-04-04 10:27:55.000123452789
What I want to, if I already use 12352 id I don't want to use again.
Please help me out to fix this issue.
April 7, 2017 at 12:03 am
The reason that you are getting duplicates is because your source data is all the same. If you want to prevent duplicates in your results, then you need to find a way to remove the duplicates in your source data.
The only difference between the following records is the s_key value.
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123456'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123457'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123458'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123452'
To de-duplicate the data, you need to provide the logic of which record to choose.
Test SQL
WITH cteDuplicates AS (
SELECT
S1.s_key
, D2.d_id
, rn = ROW_NUMBER() OVER (PARTITION BY D2.d_id
ORDER BY S1.s_key DESC /* Use the record with the lowest value */
--ORDER BY S1.s_key /* Use the record with the highest value */
--ORDER BY NEWID() /* Use a random record */
)
FROM #Source_table1 S1
INNER JOIN #dest_table2 D2
ON S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)
)
SELECT *
FROM cteDuplicates
WHERE rn = 1
Update SQL
SELECT 'BEFORE', * FROM #dest_table2;
WITH cteDuplicates AS (
SELECT
S1.s_key
, D2.d_id
, rn = ROW_NUMBER() OVER (PARTITION BY D2.d_id
ORDER BY S1.s_key DESC /* Use the record with the lowest value */
--ORDER BY S1.s_key /* Use the record with the highest value */
--ORDER BY NEWID() /* Use a random record */
)
FROM #Source_table1 S1
INNER JOIN #dest_table2 D2
ON S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)
)
UPDATE dest
SET d_s_key = src.s_key
FROM #dest_table2 AS dest
INNER JOIN cteDuplicates AS src
ON dest.d_id = src.d_id
WHERE src.rn = 1;
SELECT 'AFTER', * FROM #dest_table2;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply