CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Imran Azam
Imran Azam

Reputation: 89

SQL Server Query-Update Duplicate Records

Could you please anyone help me out with SQL Server query. I want to insert Table 1 records to Table 2 as per business rule.

Business Rule1- If the Ref_No. is same and MsgCreatedDt are different then insert those records and update very first record (Remarks column) as blank and rest as duplicate.

Business Rule2- If the Ref_No. is same and MsgCreatedDt are also same then insert only one record.

Below are the input and output.

Input: Table1

ID  Ref_No  MsgCreatedDt    Remarks
1   Ref1    22-03-2020 17:39    
2   Ref1    22-03-2020 17:42    
3   Ref1    22-03-2020 18:10    
4   Ref2    22-03-2020 16:11    
5   Ref2    22-03-2020 16:11

Output: Table2

ID  Ref_No  MsgCreatedDt        Remarks
1   Ref1    22-03-2020 17:39    
2   Ref1    22-03-2020 17:42    Duplicate
3   Ref1    22-03-2020 18:10    Duplicate
4   Ref2    22-03-2020 16:11

enter image description here

Upvotes: 0

Views: 61

Answers (2)

Caius Jard
Caius Jard

Reputation: 74740

Slightly simpler than a double rownumber method:

  insert into Table2
  select
    min(id),
    ref_no,
    msgcreateddt,
    case when msgcreateddt > min(msgcreateddt) over (partition by ref_no) then 'Duplicate' end 
  from Table1
  group by ref_no, msgcreateddt

It works because window functions are done after group by; the group by removes rows that have exactly duplicated dates, then the window function (min(msgcreateddt) over(partition by ref_no) - it works out the min date per ref_no) is used to work out if the current row's msgcreateddt is greater than that min date

Upvotes: 2

forpas
forpas

Reputation: 164224

With ROW_NUMBER() window function:

insert into Table2 (ref_no, msgcreateddt, remarks)
select t.ref_no, t.msgcreateddt,
  case when t.rn1 > 1 then 'Duplicate' end
from (  
  select *,
    row_number() over (partition by ref_no order by msgcreateddt) rn1,
    row_number() over (partition by ref_no, msgcreateddt order by (select null)) rn2
  from Table1
) t  
where t.rn2 = 1

See the demo.
Results:

>   ID | Ref_No | MsgCreatedDt            | Remarks  
> ---: | :----- | :---------------------- | :--------
> 1    | Ref1   | 2020-03-22 17:39:00.000 | null     
> 2    | Ref1   | 2020-03-22 17:42:00.000 | Duplicate
> 3    | Ref1   | 2020-03-22 18:10:00.000 | Duplicate
> 4    | Ref2   | 2020-03-22 16:11:00.000 | null

Upvotes: 2

Related Questions