ronmichael
3/12/2014 - 3:07 PM

Merge and delete redundant rows. Say you have a table that represents a calendar and hours worked by individuals and say you want to merge "

Merge and delete redundant rows. Say you have a table that represents a calendar and hours worked by individuals and say you want to merge "duplicate" events on the same day (same person, same day, same type of event). You want the remaining record to include the sum of all hours of all the redundant records and for the other records to be removed.

declare @personid int = 1900, @start date = '2/24/2014', @stop date ='3/2/2014';


merge people_schedules ps

using (
	select row_number() over ( partition by ps2.eventid, ps2.date order by ps2.uniqueid ) as row, ps2.uniqueid, ps2.personid, ps2.eventid, ps2.date, ps2.servicerequestid, 
		sum(ps3.labortimeregular) LaborTimeTotal,
		from People_Schedules ps2
		join People_Schedules ps3 on ps2.eventid=ps3.eventid and ps2.personid=ps3.personid and ps2.date=ps3.date
		where ps2.personid=@personid and ps2.date between @start and @stop
		group by ps2.uniqueid, ps2.personid, ps2.eventid, ps2.date
		having count(distinct ps3.uniqueid)>1
	) as data

on data.uniqueid = ps.uniqueid

when matched and data.row=1 then
	update set labortimeregular = LaborTimeTotal

when matched then
	delete 
	
;