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.
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
