SQL Query Help

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  April 6, 2017 at 10:29 pm

  #342212

  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.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  April 7, 2017 at 12:03 am

  #1937170

  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;


  ---

  How to post data/code on a forum to get the best help.