SQLTeam.com | Weblogs | Forums

How to delete duplicate entries and compare


#1

hi Guys could somebody help me,

Ids Category Properties Transaction date Remarks
12345 Pen Black 10/10/2018 New
12345 Pen Black 10/10/2018 New
12345 Pen Black 10/10/2018 New
12345 Pen Black 10/10/2018 New
12345 Pen Black 10/10/2018 New
12345 Pen Red 12/10/2018 Modified

output
12345 Pen Black 10/10/2018 New
12345 Pen Red 12/10/2018 Modified

above is data having ids of 12345 key in several times with same date. what happen is, i want to delete that duplicates which has same date and properties. the following day another entry is keyed- in once.
Now I want to compare the two entries of same ids with different properties and mark it as new or modified.
date of comparison should be the current system date vs 1 entry date older.


#2

hi

please find my solution

I am still not clear about

Now I want to compare the two entries of same ids with different properties and mark it as new or modified.
date of comparison should be the current system date vs 1 entry date older.

Create Sample Data Script
use tempdb
go

drop table #data
go

create table #data
(
Id int ,
Category	varchar(100),
TransactionDate	date ,
Remarks varchar(100)
)
go


insert into #data select 12345,'Pen	Black','10/10/2018','New'
insert into #data select 12345,'Pen	Black','10/10/2018','New'
insert into #data select 12345,'Pen	Black','10/10/2018','New'
insert into #data select 12345,'Pen	Black','10/10/2018','New'
insert into #data select 12345,'Pen	Black','10/10/2018','New'
insert into #data select 12345,'Pen	Red','12/10/2018','Modified'
go
Delete Duplicate Records
--Delete Duplicate Records 
;WITH tempemp 
     AS (SELECT id, 
                category, 
                transactiondate, 
                remarks, 
                Row_number() 
                  OVER( 
                    partition BY id, category, transactiondate, remarks 
                    ORDER BY id) AS duplicateRecCount 
         FROM   dbo.#data) 
DELETE FROM tempemp 
WHERE  duplicatereccount > 1

The case statement is where I implement your logic where i have doubt

SQL
SELECT a.id, 
       a.category, 
       a.transactiondate, 
       CASE 
         WHEN Min(a.transactiondate) >= Getdate() THEN 'New' 
         ELSE 'Modified' 
       END AS Remarks 
FROM   #data a 
       JOIN #data b 
         ON a.id = b.id 
            AND a.remarks <> b.remarks 
GROUP  BY a.id, 
          a.category, 
          a.transactiondate
Result