I can't believe I'm still doing this:

USE [collect2000]

GO

/****** Object: UserDefinedFunction [dbo].[tvf_ScrubData] Script Date: 05/21/2013 17:29:25 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tvf_ScrubData]')...

Here's some sample data to throw at it.

select * from

(

select '01/01/2013' as data

union all select '01-02-2012'

union all select '01-03-2010'

union all select '123'

union all select '-123'

union all select '10:30'

union all select...

It's an inline tvf that scrubs data by type using LIKE.

int, decimal (signed and unsigned), date, time, date time.

For those situations where data just gets stuck in a varchar field...

It doesn't...

Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.

But that query doesn't have

where not exists(select 1 from Test_Elevations where productid = field1)

like the original did.

oops, I guess it did.

Maybe if you put it at the end?

Apparently something in your select is generating duplicate rows. Try it without the INSERT and the problem should become evident. As a quick check, try adding a DISTINCT. The varchar(30)...

What do you get when you do this?

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'PK__ELEVATIONS__7B5130AA'

Nothing can take the place of someone who takes it personally when the system sucks. It may be 'only a job', but programmer isn't what I do, it's who I...

The table you're selecting from has duplicate project ids. Try

SELECT PROJECTID,COUNT(*) FROM BLAH GROUP BY PROJECTID HAVING COUNT(*) > 1

To identify the problematic row(s).

In keeping with the terseness of your post...

PIVOT

:w00t:

Adam hit it right on the head.

Why not combine not updating the time in DOB with this?

IF UPDATE(DOB)

BEGIN

UPDATE P

SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)

FROM dbo.Patient P INNER JOIN

inserted i ON

P.PatientID = i.PatientID

...

select icn, DateDiff(dd,min(fromdt),isNull(max(todt),GETDATE())) daycnt from icns

group by icn

I'm sure you've seen this hundreds of times before - you're one of...