This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.
DECLARE @Interval INT = 7
;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
SELECT ProductID,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
FROM Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
SELECT s.ProductID,
s.TransactionDate AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM cteSingle AS s
LEFT JOIN cteSingle AS t ON t.ProductID = s.ProductID
AND t.recID = s.recID - 1
WHERE DATEADD(DAY, @Interval, t.TransactionDate) < s.TransactionDate
OR t.ProductID IS NULL
), cteUpper(ProductID, EndDate, recID)
AS (
SELECT s.ProductID,
DATEADD(DAY, @Interval, s.TransactionDate) AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM cteSingle AS s
LEFT JOIN cteSingle AS t ON t.ProductID = s.ProductID
AND t.recID = s.recID + 1
WHERE DATEADD(DAY, @Interval, s.TransactionDate) < t.TransactionDate
OR t.ProductID IS NULL
)
SELECT l.ProductID,
l.StartDate,
u.EndDate
FROM cteLower AS l
INNER JOIN cteUpper AS u ON u.ProductID = l.ProductID
WHERE l.recID = u.recID
ORDER BY l.ProductID,
l.recID
Here are the measurements from SQL Profiler.
Peso
RowCount - 4020
CPU - 1560
Duration - 1856
Reads - 3367
Original
RowCount - 4020
CPU - 35303
Duration - 24216
Reads - 1976780