Given the data you posted, the expected output is 13 I hope, as there is no exclusion before the 14th.

Anyway here is a query. I'm fairly sure it can be made more efficient:

CREATE TABLE [dbo].[OrderSample](

[OrderID] [int] ,

[OrderStartDate] [datetime] ,

[OrderEndDate] [datetime]

)

--## Create sample 'order'

INSERT INTO OrderSample VALUES(14,'2013-09-01','2013-09-30')

INSERT INTO OrderSample VALUES(16,'2013-08-01','2013-09-15')

CREATE TABLE [dbo].[ExclusionSample](

[OrderID] [int] ,

[ExclusionStartDate] [datetime] ,

[ExclusionEndDate] [datetime]

)

--## Create sample 'exclusion ranges' - my product does not work these days

INSERT INTO ExclusionSample VALUES(14, '2013-09-14','2013-09-15')

INSERT INTO ExclusionSample VALUES(14, '2013-09-21','2013-09-22')

INSERT INTO ExclusionSample VALUES(14, '2013-09-28','2013-09-29')

INSERT INTO ExclusionSample VALUES(14, '2013-09-25','2013-09-25')

INSERT INTO ExclusionSample VALUES(16, '2013-08-14','2013-08-15')

INSERT INTO ExclusionSample VALUES(16, '2013-08-21','2013-08-22')

INSERT INTO ExclusionSample VALUES(16, '2013-08-28','2013-08-29')

INSERT INTO ExclusionSample VALUES(16, '2013-09-14','2013-09-14')

go

WITH CTE1 AS (

SELECT OS.OrderID, d.thedate,

active = IIF(EXISTS (SELECT *

FROM ExclusionSample ES

WHERE OS.OrderID = ES.OrderID

AND d.thedate BETWEEN ES.ExclusionStartDate AND ExclusionEndDate), 0, 1)

FROM dates d

JOIN OrderSample OS ON d.thedate BETWEEN OS.OrderStartDate AND OrderEndDate

), CTE2 AS (

SELECT OrderID, active,

SUM(active) OVER (PARTITION BY OrderID ORDER BY thedate

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumactive,

row_number() OVER (PARTITION BY OrderID ORDER BY thedate) AS rowno

FROM CTE1

), CTE3 AS (

SELECT OrderID, active, cumactive, rowno,

denserank = dense_rank () OVER (PARTITION BY OrderID ORDER BY cumactive)

FROM CTE2

), CTE4 AS (

SELECT OrderID, rowno - denserank AS grp, COUNT(*) AS cnt

FROM CTE3

WHERE active = 1

GROUP BY OrderID, rowno - denserank

)

SELECT OrderID, MAX(cnt)

FROM CTE4

GROUP BY OrderID

go

DROP TABLE OrderSample

DROP TABLE ExclusionSample

The query includes the table dates, here is a script (not this year's model) to populated it:

-- Make sure it's empty.

TRUNCATE TABLE dates

go

-- Get a temptable with numbers. This is a cheap, but not 100% reliable.

-- Whence the query hint and all the checks.

SELECT TOP 80001 n = IDENTITY(int, 0, 1)

INTO #numbers

FROM sysobjects o1

CROSS JOIN sysobjects o2

CROSS JOIN sysobjects o3

CROSS JOIN sysobjects o4

OPTION (MAXDOP 1)

go

-- Make sure we have unique numbers.

CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)

go

-- Verify that table does not have gaps.

IF (SELECT COUNT(*) FROM #numbers) = 80001 AND

(SELECT MIN(n) FROM #numbers) = 0 AND

(SELECT MAX(n) FROM #numbers) = 80000

BEGIN

DECLARE @msg varchar(255)

-- Insert the dates:

INSERT dates (thedate)

SELECT dateadd(DAY, n, '19800101')

FROM #numbers

WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into #numbers'

PRINT @msg

END

ELSE

RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)

go

DROP TABLE #numbers