What's wrong with a set-based solution?SELECTCASE

WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))

ELSE 0

END

FROM(

VALUES(@s1, @f1),

(@s2, @f2)

) AS d(FromTime, ToTime);You can wrap this as an ITVF.

SELECTMarker

FROMdbo.TEMP_A

GROUP BYMarker

HAVINGSUM(CASE WHEN Val = 'Y' THEN 0 ELSE 1 END) = 0

No. You are right.

I am the one needing more coffee. Just a mind lapse and disregarding the DISTINCT for some reason.

So, depending on the distribution of the sample data, the two different queries will return different results.

The COUNT(DISTINCT ... ) will return a fewer number of rows, than the SUM(CASE...

You don't need to.

The COUNT(DISTINCT ...) approach will only return the groups that has exactly one A and one B.

The SUM(CASE ...) approach will return all groups having at least...

They are not equivalent and return...

It boils down to the number of IO used for the solution.

Also the CPU usage matters.

Obviously scanning the table twice (using INTERCEPT/EXCEPT for example) uses twice as much IO than...

There is no guarantee that customer with both product A and B are returned.

SimonC, I have to disagree with you here.

Even if the PIVOT solution seem to work well, it doesn't scale well. And that is one of the cardinal sins junior developers...

See page 10.

SELECT CustomerID

FROM #Purchase

WHERE ProductCode IN ('A', 'B', 'C')

GROUP BY CustomerID

HAVING MIN(ProductCode) = 'A'

AND MAX(ProductCode) = 'C'

AND SUM(CASE WHEN ProductCode = 'B' THEN 1 ELSE...

You know me Jeff 🙂 Can't help myself since this is a Relational Division Problem.

SELECTCustomerID

FROM#Purchase

WHEREProductCode IN ('A', 'B', 'C')

GROUP BYCustomerID

HAVINGMIN(ProductCode) = 'A'

AND MAX(ProductCode) = 'B'

--AND COUNT(*) = 2;

Here is a link to download the schema of my hybrid solution.

It can also be found on my web page http://www.sqltopia.com

Thank you Jeff!

The client I was working with have 35,000 employees of which about 15,000 are using the database at any given time. And the system is, by nature, a...

No problem.