That's correct - I'd expect 13 as the result from the sample data.
This looks like a more distilled, set based version of what I'm doing - exactly what I was after. I'm going to go through this and really check it out, see how it performs up against the larger sample set I have. Very much appreciated - thank you!!
Erland Sommarskog (9/13/2013)
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