Delete a duplicate, replace with null!

[proximity]

Hi,

Here's a real stumper! In the data below, I want to remove the values that are duplicated, except for the first instance of the duplicate, and replace them with null!


Before
ID Emp Job StartTime EndTime Qty
46371 431172 476628 20/07/2010 23:35:05 20/07/2010 23:37:56 0
46372 431172 476626 20/07/2010 23:35:05 20/07/2010 23:37:56 0
46374 431168 476515 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46375 431168 476514 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46376 431168 476624 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46377 431168 476634 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46379 429545 476825 20/07/2010 23:38:44 21/07/2010 01:55:35 5
46380 429545 476828 20/07/2010 23:38:44 21/07/2010 01:55:35 5
46381 429545 476827 20/07/2010 23:38:44 21/07/2010 01:55:35 5
46382 429545 476833 20/07/2010 23:38:44 21/07/2010 01:55:35 5


After
ID Emp Job StartTime EndTime Qty
46371 431172 476628 20/07/2010 23:35:05 20/07/2010 23:37:56 0
46372 431172 476626
46374 431168 476515 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46375 431168 476514
46376 431168 476624
46377 431168 476634
46379 429545 476825 20/07/2010 23:38:44 21/07/2010 01:55:35 5
46380 429545 476828
46381 429545 476827
46382 429545 476833


If anyone can come up with an idea, I would be most grateful!

Thanks,
--
Steven

 

[Remou]

How about:

UPDATE Log AS a 
SET a.StartTime = Null, a.EndTime = Null, a.Qty = Null
WHERE a.ID Not In (
     SELECT TOP 1 Id
     FROM Log b 
     WHERE b.emp = a.emp
     ORDER BY b.Id)

http://lessthandot.com

 

[proximity]

Thanks Remou,

That is a brilliant suggestion and it mostly works! However, it seems to skip huge chunks of data and I have no idea why, as in the example 'after' below:

After
ID Emp Job StartTime EndTime Qty
46371 431172 476628 20/07/2010 23:35:05 20/07/2010 23:37:56 0
46372 431172 476626
46374 431168 476515 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46375 431168 476514
46376 431168 476624
46377 431168 476634
46379 429545 476825 this is missing the values
46380 429545 476828
46381 429545 476827
46382 429545 476833

Any ideas?

--
Steven

 

[Remou]

That is not the result I am getting. I did not make any allowances for the same job having different start and end times, as that is not shown in your sample data. Did you get the same problem with exactly the sample you show?

[tt]
ID Emp Job StartTime EndTime Qty
46371 431172 476628 20/07/2010 23:35:05 20/07/2010 23:37:56 0
46372 431172 476626
46374 431168 476515 20/07/2010 23:38:18 20/07/2010 23:47:16 0
46375 431168 476514
46376 431168 476624
46377 431168 476634
46379 429545 476825 20/07/2010 23:38:44 21/07/2010 01:55:35 5
46380 429545 476828
46381 429545 476827
46382 429545 476833 [/tt]

http://lessthandot.com

 

[proximity]

Hi Remou,

Yes, the same problem with the sample data. Looking at it again, maybe I worded it wrongly? Each employee has multiple jobs and each of those jobs has the same start/end and qty value. So, at each change of employee, we only null those values after the first one and repeat . . . . . . !