SQLTeam.com | Weblogs | Forums

Removing duplicates


#1

Hi,
I have data like this:
emplid,stardt,enddt,hoursworked
1,2/1/17,2/1/17,7.5
1,2/2/17,2/2/17,7.5
1,2/1/17,2/2/17,15
It is a mix of daily hours and weekly totals. How can I remove daily hours for the same period weekly total exists.
Any help is greatly appreciated.
Thanks in advance.


#2

delete duplicates from table

WITH cte
AS (SELECT sessionid, ROW_NUMBER() OVER (PARTITION BY sessionid
ORDER BY ( SELECT 0)) RN
FROM chargecapture
where cast([Delivery Date] as date) ='2016-08-31' and [Total Neonates]=1)
DELETE FROM cte
WHERE RN > 1;

WITH cte
AS (SELECT sessionid,[neonate#],[Total Neonates], ROW_NUMBER() OVER (PARTITION BY sessionid
ORDER BY ( SELECT 0)) RN
FROM CHARGECAPTURE
where cast([Delivery Date] as date) >='2016-10-10' and SessionID =2046011 and [Total Neonates]=2)
DELETE FROM cte
WHERE RN > 2;


#3
-- Delete duplicates TEMPLATE
-- BEGIN TRANSACTION
;WITH CTE_Delete
AS
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
		OVER (
			PARTITION BY MatchColumn1
				, MatchColumn2
			ORDER BY MatchColumn1
				, MatchColumn2
-- ** NOTE: This Sort Order must present the row to RETAIN FIRST
				, TieBreakColumn1
				, TieBreakColumn2
		)
--	SELECT TOP 10 *
	FROM	dbo.TableContainingDuplicates AS D
--	Optional Filter:
	WHERE	MatchColumn1 LIKE 'SomeValue%'
)
DELETE D
-- SELECT TOP 100 *
FROM	CTE_Delete AS D
WHERE	1=1
	AND T_RowNumber >= 2
--
-- COMMIT
-- ROLLBACK

#4

If you want to filer out "Daily" rows:

SELECT emplid,stardt,enddt,hoursworked
FROM
   Table1
WHERE
stardt<>enddt;

If you want to delete the "Daily" rows:

DELETE
   Table1
WHERE
   stardt=enddt;

#5

Thank you so much for the response. I am sorry I wasn't clear on the problem. We are initially storing weekly total hours worked and later changed the criteria to store daily hours worked. when this change happened, the developer stored daily hours and weekly totals for few months. I have to remove the rows where overlap happened like the data shown below:
for the week of 1/1 to 1/7, I have daily rows from Mon-Fri (1/2-1/6) and a row for the week Sun-Sat(1/1-1/7) and the following week I have just daily rows. If I use your first qry, I don't get the second week rows, and If I use your second qry, I am going to delete all daily hours in addition to problem rows. I have to keep either daily rows or weekly total row for weeks that have both values.
Hope I explained the problem clearly.

EmplID StartDate EndDate Hoursworked
1 1/2/2017 1/2/2017 7
1 1/3/2017 1/3/2017 7
1 1/4/2017 1/4/2017 7
1 1/5/2017 1/5/2017 7
1 1/6/2017 1/6/2017 7
1 1/1/2017 1/7/2017 35
1 1/8/2017 1/8/2017 7
1 1/9/2017 1/9/2017 7
1 1/10/2017 1/10/2017 7
1 1/11/2017 1/11/2017 7
1 1/12/2017 1/12/2017 7
1 1/13/2017 1/13/2017 7


#6

Thank you for the response Kristen. I am sorry, I did not explain my problem clearly. Please see my response below to joterre's message.