David wrote... "If I was to use the static table approach I would probably stick it in its own database so that it wouldn't artificially inflate the backups."

Yep, I know...

Thank you for the very nice compliment... I only hope it does what you need

Well done, David.  It's nice to see it when someone takes multiple methods and does a really good performance comparison.

It seems that generating a range of numbers in a temporary...

...and thank you for the feedback...

By the way, if you do put such an index on the table, you will never have duplicates again.

I only count 2 extra rows but that may be due to a simple lack of coffee...

Anyway... I think this might be what...

In that case... this works... there may be a correlated sub-query solution but I didn't use one... obviously, you would replace #yourtable with your real table name after you're done...

Shane,

Do you want only the downtime between 10:00 and 11:00 or do you want the downtime for every hour of a given day?  Also, is there a date associated with...

What day of the week do you consider the first day of the week to be?

What is it that distiguishes CaseID 2 from 1 and 3?  As in your first example of data, what connects ANY 2 calls (CaseID's) if multiple calls meet the same conditions?

Guess I read it differently, Marshall... I didn't read the original request as a full transposition based on what some folks have asked for in the past...  Not enough coffee

Just a follow up... I did some million row testing on the simple functions we created here...

On a quiet multiprocessor box, the functions returned about 12% faster than the inline...

Untested but should work...

 SELECT d.Value AS [IN Statement],
        CASE 
            WHEN ta.Col_1 IS NULL
            THEN 'No Match'
            ELSE ta.Col_1
        END AS [Values Col_A Matches]
   FROM TabA ta
  RIGHT OUTER JOIN
        (--Derived...

Unless your data actually looks more like the following, the code is going to be a wee bit more complicated than what I have below...

TxnNo    Amount   Type

100        1500       R

100       ...

You shouldn't necessarily need a function to convert a VARCHAR date to a datetime to save in a table... it's one of those "instrinsic conversions" that happen auto-magically if the...

KH did it right... if you expand upon the example KH provided, it works great, is nasty fast, and does not require a trip to the app.