create table SaleLocationXref (
 SaleID int not null,
 LocationID int not null,
 constraint SaleLocationXref_PK primary key (SaleID,LocationID)
);
create table Sale (
 SaleID int not null,
 SaleStart datetime not null,
 SaleEnd datetime not null,
 constraint Sale_PK primary key (SaleID)
);
create table SaleItemXref (
 SaleID int not null,
 ItemID int not null,
 constraint SaleItemXref_PK primary key (SaleID,ItemID)
);

insert into SaleLocationXref
values (1,1),(2,1),(4,1),(5,1),(3,2);

insert into Sale
values (1,'01/01/2012','01/07/2012'),
       (2,'01/06/2012','01/10/2012'),
       (3,'01/01/2012','02/07/2012'),
       (4,'02/08/2012','02/15/2012'),
       (5,'01/09/2012','01/16/2012');

insert into SaleItemXref
values
    (1,1),(1,2),(1,3),
    (2,1),(2,4),(2,5),
    (3,5),(3,6),(3,7),
    (4,5),(4,6),(4,7),
    (5,7),(5,8),(5,1);
GO

with BaseData as (
select
    six.ItemID,
    six.SaleID,
    s.SaleStart,
    s.SaleEnd,
    count(*) over (partition by six.ItemID) as ItemCnt
from
    Sale s inner join SaleItemXref six
        on (s.SaleID = six.SaleID)
)
select distinct
    bd1.ItemID,
    bd1.SaleID,
    slx.LocationID,
    bd1.SaleStart,
    bd1.SaleEnd
from
    BaseData bd1
    inner join BaseData bd2
        on (bd1.ItemID = bd2.ItemID
            and bd1.SaleID <> bd2.SaleID)
    inner join SaleLocationXref slx
        on (slx.SaleID = bd1.SaleID)
where
    bd1.ItemCnt > 1
    and (bd1.SaleStart between bd2.SaleStart and bd2.SaleEnd or
         bd2.SaleStart between bd1.SaleStart and bd1.SaleEnd)
order by
    bd1.ItemID,
    bd1.SaleID;
GO

drop table SaleLocationXref;
drop table Sale;
drop table SaleItemXref;
GO

;WITH Sale AS (
    SELECT a.SaleID, a.SaleStart, a.SaleEnd, b.LocationID, c.ItemID
    FROM #Sale a
    JOIN #SaleLocationXref b ON a.SaleID = b.SaleID
    JOIN #SaleItemXref c ON c.SaleID = a.SaleID
    )
SELECT SaleID, LocationID
FROM (
    SELECT a.SaleID, a.SaleStart, a.SaleEnd, a.LocationID, a.ItemID
        , SaleID2=b.SaleID, LocationID2=b.LocationID, ItemID2=b.ItemID
        ,n=ROW_NUMBER() OVER (PARTITION BY a.SaleID, a.LocationID ORDER BY (SELECT NULL))
    FROM Sale a
    CROSS APPLY (
        SELECT SaleID, LocationID, ItemID
        FROM Sale b
        WHERE (b.SaleStart BETWEEN a.SaleStart AND b.SaleEnd OR
            b.SaleEnd BETWEEN a.SaleStart AND b.SaleEnd) AND a.SaleID <> b.SaleID) b 
    WHERE a.LocationID = b.LocationID AND a.ItemID = b.ItemID) c
WHERE n=1

create table #SaleLocationXref (
 SaleID int not null,
 LocationID int not null,
 constraint SaleLocationXref_PK primary key (SaleID,LocationID)
)
create table #Sale (
 SaleID int not null,
 SaleStart datetime not null,
 SaleEnd datetime not null,
 constraint Sale_PK primary key (SaleID)
)
create table #SaleItemXref (
 SaleID int not null,
 ItemID int not null,
 constraint SaleItemXref_PK primary key (SaleID,ItemID)
)

insert into #SaleLocationXref values (1,1),(2,1),(4,1),(5,1),
 (3,2)

insert into #Sale values (1,'01/01/2012','01/07/2012'),
 (2,'01/06/2012','01/10/2012'),
 (3,'01/01/2012','02/07/2012'),
 (4,'02/08/2012','02/15/2012'),
 (5,'01/09/2012','01/16/2012')

insert into #SaleItemXref values (1,1),(1,2),(1,3),
 (2,1),(2,4),(2,5),
 (3,5),(3,6),(3,7),
 (4,5),(4,6),(4,7),
 (5,7),(5,8),(5,1)

;WITH Tally (n) AS (
    SELECT TOP 10000 10+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #SaleLocationXref
SELECT DISTINCT SaleID * n, LocationID * n
FROM #SaleLocationXref
CROSS APPLY Tally

;WITH Tally (n) AS (
    SELECT TOP 10000 10+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #Sale
SELECT SaleID, SaleStart, SaleEnd
FROM (
    SELECT SaleID=SaleID * n, SaleStart, SaleEnd
        ,n=ROW_NUMBER() OVER (PARTITION BY SaleID * n ORDER BY (SELECT NULL))
    FROM #Sale
    CROSS APPLY Tally) a
WHERE n = 1

;WITH Tally (n) AS (
    SELECT TOP 10000 10+ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #SaleItemXref
SELECT DISTINCT SaleID * n, ItemID * n
FROM #SaleItemXref
CROSS APPLY Tally

DECLARE @Holder1 INT, @Holder2 INT

PRINT '---- Revised query'
SET STATISTICS TIME ON
;WITH Sale AS (
    SELECT a.SaleID, a.SaleStart, a.SaleEnd, b.LocationID, c.ItemID
    FROM #Sale a
    JOIN #SaleLocationXref b ON a.SaleID = b.SaleID
    JOIN #SaleItemXref c ON c.SaleID = a.SaleID
    )
SELECT @Holder1=SaleID, @Holder2=LocationID
FROM (
    SELECT a.SaleID, a.SaleStart, a.SaleEnd, a.LocationID, a.ItemID
        , SaleID2=b.SaleID, LocationID2=b.LocationID, ItemID2=b.ItemID
        ,n=ROW_NUMBER() OVER (PARTITION BY a.SaleID, a.LocationID ORDER BY (SELECT NULL))
    FROM Sale a
    CROSS APPLY (
        SELECT SaleID, LocationID, ItemID
        FROM Sale b
        WHERE (b.SaleStart BETWEEN a.SaleStart AND b.SaleEnd OR
            b.SaleEnd BETWEEN a.SaleStart AND b.SaleEnd) AND a.SaleID <> b.SaleID) b 
    WHERE a.LocationID = b.LocationID AND a.ItemID = b.ItemID) c
WHERE n=1
SET STATISTICS TIME OFF

PRINT '---- Original query'
SET STATISTICS TIME ON
select distinct @Holder1=SaleID, @Holder2=LocationID
 from (
 select SIX.SaleID, LocationID, ItemID,
 Count(*) over (partition by LocationID, ItemID) as Cnt
 from #SaleItemXref SIX
 join (
 select distinct S1.SaleID, SLX1.LocationID 
from #Sale S1
 join #Sale S2 -- Compare sales
 on S1.SaleID <> S2.SaleID
 and (S1.SaleStart <= S2.SaleEnd -- Compare dates overlap
 and S2.SaleStart <= S1.SaleEnd)
 join #SaleLocationXref SLX1
 on S1.SaleID = SLX1.SaleID
 join #SaleLocationXREF SLX2
 on S2.SaleID = SLX2.SaleID
 and SLX1.LocationID = SLX2.LocationID -- Compare location overlap
 ) #SaleOverlap
 on #SaleOverlap.SaleID = SIX.SaleID
 ) ItemOverlap
 where ItemOverlap.Cnt > 1 -- Compare item overlap
SET STATISTICS TIME OFF

PRINT '---- Original query with MAXDOP 1'
SET STATISTICS TIME ON
select distinct @Holder1=SaleID, @Holder2=LocationID
 from (
 select SIX.SaleID, LocationID, ItemID,
 Count(*) over (partition by LocationID, ItemID) as Cnt
 from #SaleItemXref SIX
 join (
 select distinct S1.SaleID, SLX1.LocationID 
from #Sale S1
 join #Sale S2 -- Compare sales
 on S1.SaleID <> S2.SaleID
 and (S1.SaleStart <= S2.SaleEnd -- Compare dates overlap
 and S2.SaleStart <= S1.SaleEnd)
 join #SaleLocationXref SLX1
 on S1.SaleID = SLX1.SaleID
 join #SaleLocationXREF SLX2
 on S2.SaleID = SLX2.SaleID
 and SLX1.LocationID = SLX2.LocationID -- Compare location overlap
 ) #SaleOverlap
 on #SaleOverlap.SaleID = SIX.SaleID
 ) ItemOverlap
 where ItemOverlap.Cnt > 1 -- Compare item overlap
OPTION (MAXDOP 1)
SET STATISTICS TIME OFF

drop table #SaleLocationXref
drop table #Sale
drop table #SaleItemXref

---- Revised query

 SQL Server Execution Times:
   CPU time = 2791 ms,  elapsed time = 5481 ms.
---- Original query

 SQL Server Execution Times:
   CPU time = 3403 ms,  elapsed time = 974 ms.
---- Original query with MAXDOP 1

 SQL Server Execution Times:
   CPU time = 1840 ms,  elapsed time = 1883 ms.

SELECT DISTINCT @holder1 = s1.SaleID,@holder2 = LocationID
FROM #Sale s1
	INNER JOIN #SaleLocationXref SLX1 ON S1.SaleID = SLX1.SaleID
	INNER JOIN #SaleItemXref SIX ON SIX.SaleID = s1.SaleID
WHERE EXISTS(
			SELECT 1 
			FROM #Sale s2 
				INNER JOIN #SaleLocationXref SLX2 ON s2.SaleID = SLX2.SaleID
				INNER JOIN #SaleItemXref SIX2 ON SIX2.SaleID = s2.SaleID
			WHERE s1.SaleID <> s2.SaleID
				AND s1.SaleStart <= s2.SaleEnd
				AND s2.SaleStart <= s1.SaleEnd
				AND SLX2.LocationID = SLX1.LocationID
				AND SIX.ItemID = SIX2.ItemID)
GROUP BY s1.SaleID,LocationID