^^^ lol. I need to change the moniker.

Here is a revised version eliminating the GROUP BY and/or DISTINCT clauses. I especially dislike using either of those to return non-aggregated data. Most of the time there is always a better and more efficient way to write the query.

SELECT s1.SaleID,LocationID

FROM #Sale s1

INNER JOIN #SaleLocationXref SLX1 ON S1.SaleID = SLX1.SaleID --Find location

WHERE EXISTS(

SELECT 1

FROM #Sale s2

INNER JOIN #SaleLocationXref SLX2 ON s2.SaleID = SLX2.SaleID --Find location

INNER JOIN #SaleItemXref SIX2 ON SIX2.SaleID = s2.SaleID --Find item

WHERE s1.SaleID <> s2.SaleID --Make sure that we are comparing two different SaleIDs

AND s1.SaleStart <= s2.SaleEnd --Make sure that they fall within date ranges of each other

AND s2.SaleStart <= s1.SaleEnd --^^

AND SLX2.LocationID = SLX1.LocationID --Where s1 and s2 have the same location id

AND EXISTS(

SELECT 1

FROM #SaleItemXref SIX

WHERE SIX.SaleID = s1.SaleID --Find sale item for record s1

AND SIX.ItemID = SIX2.ItemID --Make sure sale item from s1 has a matching record in s2

)

)