Please provide some ready to use sample data for your current_visit_test table in the form of

INSERT INTO...

SELECT... UNION ALL

SELECT...

together with your expected result based on those sample data.

You might...

Most important thing first:

You need to make sure to use the same data type in each part of a CASE statement, otherwise SQL Server will perform an implicit conversion.

In the...

Unfortunately, I don't know how your original data look like (you refer to "above" but you have two very different sample rows in that post.)

Some of your sample data have...

Please provide table def and sample data for your UserTracking table together with some sample data and expected result based on your sample. (you did an excellent job posting the...

@Lowell:

That's a much more precise way to describe what I was talking about.

Nice job!!

You could use dynamic SQL together with sys.columns. You could check system_type_id to modify just the related data types.

However, I don't know why you want to "remove decimal points". Do...

There are several solutions:

Customized messages: use customized error messages together with RAISEERROR and make sure to include the error number inside the error message. This will allow you to get...

It seems like you have both types of values in that column.

Therefore, the first select will fail for the second type of data and vice versa.

If you always have the...

Hi Jeff,

attached the results for version 2 (19min 35sec).

Side note: I guess you figured the reason why I mentioned the Profiler trace in my previous post... 😉

Of course I did...

GilaMonster (9/19/2010)

:w00t:

Should...

Hi Jeff,

results attached.

System: WIN XP SP3

Hardware: DELL Vostro, 2QuadCPU @ 2.4GHz, 3.25Gb RAM

Profiler trace available, if needed.

The OVER () clause only works directly related to a SUM() or any other aggreagtion.

You can't apply it to (more or less) complex calculations.

What you could try is adding the...

CirquedeSQLeil (9/17/2010)

:blush:

[cringingness ON]

I'm glad being recognized by an SSChampion to be of some sort of help. 😀

[cringingness OFF]

More serious: Congrats for being the 6th person climbing...

I spotted 4 c.u.r.s.o.r.s being part of that sproc but you already mentioned you're going to work on it. I think it's the major part of the poor performance.

The function...