Although the DATEADD/DATEDIFF method will sometimes return a faster duration, it is slower than "convert(datetime,convert(int,GetDate()-.5))" if you consider actual resources used like CPU Seconds and DIsk Reads and Writes.  Here's...

I just can't bring myself to trust anything in an article where the author makes mistakes like the following...

"Remember that the BETWEEN clause retrieves values that are equal...

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       ...