Jeff Moden (2/5/2014)

You obviously didn't hear that from me. 😉 There's absolutely...

I'm pleasantly surprised, too. I wonder if this would stop people from improving working solutions.

Something like this?

SELECT *,

LEFT( REPLACE( REPLACE( COMMAND, 'exec ',''), 'execute ','') --Eliminate EXEC or EXECUTE

, PATINDEX( '%[ (]%', REPLACE( REPLACE( COMMAND, 'exec ',''), 'execute ','')) - 1) --Find the...

With 800M rows, I would use a bulk export/import for this. I see 2 options:

Use bcp to export query with a SELECT DISTINCT (or GROUP BY all columns), truncate the...

I would like to thank Alex for this article. I know that a lot of people can get an idea of the options listed in here.

I agree, however, that it...

I managed to get into your head. I hope you won't have nightmares :hehe:

I agree that your expected results are wrong because you have an enddate greater than the startdate and that will generate a problem in the next row (rows 3 &...

Depending on what you need, you might want to take a look at TRY_CAST() or TRY_CONVERT()

I might not be realizing the problem that Sean is talking about, but here's an example on how to do it. Be sure to understand what's going on before using...

And this is why I really love cross-tabs. Pivot becomes completely annoying with multiple columns or calculations being pivoted. I struggled to get right the pivot approach.

Again, any comments are...

It seems that I get very similar results with pivot and cross tabs. The parallelism is created with the pre-aggregation because the normal pivot and cross tabs won't use it....

I have nothing to test, but a single call to the function might work better.

SELECT t1.[Text] ...

Have you read about NULLIF?

You could use something like this:

CASE WHEN MAX(PutchaseAmount) = -1 /*AND MIN( PutchaseAmount) = -1*/ --Uncomment if you have other negative values

THEN -1

ELSE ISNULL(AVG(NULLIF(PutchaseAmount ,-1)), 0)...

Yes, converting to float won't be useful because the aggregate will continue to consider it. You need to convert it to a null value to prevent that. Or you need...

Here's a possible solution, it's not pretty but it's better than my first version full of functions on the columns.

DECLARE @Timetime = '03:30:00';

WITH Shift_table(Shiftno, starttime, endtime) AS(

SELECT 1, CAST( '06:00:00'...