Thanks Noel, I'll give that a shot.

WHY 10 sec interval?

We operate a real time data entry production line where literally every second counts.  If an operator has to wait 1 sec or egad 5 sec between...

Thanks.  I ended up writing my own and added it to the end of this post here. 

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=161298

There was another good solution in there too.

- John

I wrote this to solve the same problem.  Mine is not as thorough since it doesnt drop foriegn keys, which I think would be a good thing.  I opted not...

thanks to both of you!  Both of those articles were very helpful.

I think this would work:

UPDATE Enrollment

SET ISMID = NULL

WHERE ENRid NOT IN (

  SELECT MIN(E.ENRid)

  FROM Enrollment

  WHERE NOT ISMid IS NULL

  GROUP BY ISMid, [Date]

)

Yeah, I would use ISNULL instead of COALESCE in this case, but be aware of frank's above post producing the different results.

I noticed something interesting.  You can delcare the variable...

haha, my method is clearly described in the earlier post.  Sorry. 

DECLARE @sList VARCHAR(8000)

SELECT @sList = COALESCE(@sList + ',','') + SomeField FROM SomeTable

did you try cast to float/int?

Casting to strings, calling convert would be slow. 

I bet CAST(CAST(SomeDate AS FLOAT) AS INT) is faster than using DateAdd...

- John

haha!  So true....  However i wonder if they designed TSQL from many different directions and it didnt line up when they met in the middle....

I just wanted to add the reason just casting to int does not work is that for some reason casting a date to an int rounds the number rather than...

sorry about the bad advice.  I was working off memory.  I think I remember that we cast to float first then cast to int.  But using it as a number...

Some more info:

If you want to count all items in a certain date, there is no need to spend cpu cycles casting back to datetime.

SELECT COUNT(*) FROM SomeTable Group By CAST(SomeTable.SomeDate...

date is really a float where the decimal part is the time.  To convert a date to date only, do this:

CAST(CAST(GetDate() AS INT) AS DATETIME)

Bam!

- John