Since you are using SS2012, here is a possible solution using the offset function LEAD.

Supposing all exclusions are inside the order interval, the idea is to generate the intervals between exclusions, which is calculated as the [ExclusionEndDate] plus 1 as the next valid [OrderStartDate] and the next [ExclusionStartDate] minus one as the valid [OrderEndDate]. We do not have more exclusions after the last one so we will use the [OrderEndDate] from the [OrderSample] table.

There is still pending how to generate the first valid range since we are looking forward on the exclusions so we are missing the range from the start date of the order till the start date of the first exclusion. Well, I tried combining the table [OrderSample] with the join of this table and the exclusions, then I use the LEAD to find next exclusion.

Excuse me for the formatting because I do not know how to post T-SQL code. I'll ask Erland for help 🙂

SET NOCOUNT ON;

USE tempdb;

GO

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 c1 AS (

SELECT

OrderID,

OrderStartDate,

OrderEndDate,

NULL AS ExclusionStartDate,

NULL AS ExclusionEndDate

FROM

dbo.OrderSample

UNION ALL

SELECT

OS.OrderID,

OS.OrderStartDate,

OS.OrderEndDate,

ES.ExclusionStartDate,

ES.ExclusionEndDate

FROM

dbo.OrderSample AS OS

INNER JOIN

dbo.ExclusionSample AS ES

ON ES.OrderID = OS.OrderID

),

C2 AS (

SELECT

OrderID,

DATEDIFF([day],

ISNULL(DATEADD([day], 1, ExclusionEndDate), OrderStartDate),

ISNULL(DATEADD([day], -1, LEAD(ExclusionStartDate) OVER(PARTITION BY OrderID ORDER BY ExclusionStartDate)), OrderEndDate)

) + 1 AS ConsecValidDates

FROM

C1

)

SELECT

OrderID,

MAX(ConsecValidDates) AS MaxConsecValidDates

FROM

C2

GROUP BY

OrderID;

GO

DROP TABLE dbo.OrderSample, dbo.ExclusionSample;

GO