Delete duplicate records, convert the datetime to date part only, update the master table by using the aggregate function for child table, update a record using joins with other table, and check duplicates

SQLServerTricks

BEGIN TRAN
--This is a sample to delete a duplicate ticket
--Assuming the duplicates are just maximum count as 2
--But if it, more than two run this check several times, because it gets only the MIN value
DELETE FROM dbo.TicketDetails
WHERE Id IN (

  SELECT 
	MIN(t.Id) as Id
	FROM dbo.TicketDetails t
	INNER JOIN 
	(  
	  select  
		ProducerId,
		ProducerUnitId,
		GradeTypeId, 
		CONVERT(date, PickupTS) as PickupTs, -- Convert the datetime to date format only for SQL Server 2008 and up
		COUNT(*) as DuplicateCount
	  from dbo.TicketDetails
	  group by  ProducerId,ProducerUnitId,GradeTypeId, CONVERT(date, PickupTS)
	  having count(*) > 1
	 ) as td on 
	 
	 t.ProducerId = td.ProducerId and
	 t.ProducerUnitId = td.ProducerUnitId and
	 t.GradeTypeId = td.GradeTypeId and
	 CONVERT(date, t.PickupTS) = td.PickupTs

	GROUP BY
	td.ProducerId,td.ProducerUnitId,td.GradeTypeId,td.PickupTs
 )
 
 
 --After this make sure the sum is also updated for ticket delivery and ticket master
 --This is a sample to update the master table by summing up the field in child table and store it to
 --specific column in master table
  UPDATE TicketMasters SET TotalLbs = td.SumOfPounds
  FROM TicketMasters tmm
	 INNER JOIN  
	 (
		select sum(Pounds) as SumOfPounds, tm.Id
		FROM TicketMasters tm inner join dbo.TicketDetails td on tm.Id = td.TicketMasterId
		group by tm.Id 
	 ) td on td.Id = tmm.Id
	 

  -- Update the ticket delivery to be the same value as the ticket master
  -- This is a sample to update the table using the joins with other table
  UPDATE dbo.TicketDeliveries SET Pounds = tmm.TotalLbs
  FROM TicketMasters tmm INNER JOIN dbo.TicketDeliveries td on 
  td.TicketMasterId = tmm.Id
 
 
 
 --Check if are still duplicate in the record
  select  ProducerId,ProducerUnitId,GradeTypeId, CONVERT(date, PickupTS) as PickupTs, count(*) as DuplicateCount
  from dbo.TicketDetails
  group by  ProducerId,ProducerUnitId,GradeTypeId, CONVERT(date, PickupTS)
  having count(*) > 1
 
   
 ROLLBACK TRAN