Here's another

WITH CTE AS (

SELECT

r.RequestId

,q.QuoteId

,r.EmailAddress

,q.CreatedAt

,CASE

WHEN DATEDIFF(dd,LAG(q.CreatedAt,1) OVER (PARTITION BY r.EmailAddress ORDER BY q.CreatedAt),q.CreatedAt) > 30

THEN 1

ELSE 0

END AS GroupResetFlag

,DENSE_RANK() OVER(PARTITION BY r.EmailAddress ORDER BY q.CreatedAt,q.QuoteId) AS InitialRank

FROM

Request r

INNER JOIN Quote q

ON r.RequestId = q.RequestId)

SELECT r.RequestId

,r.QuoteId

,r.EmailAddress

,r.CreatedAt

,r.GroupResetFlag

,r.InitialRank - ISNULL(ca.InitialRank-1,0) AS InitialRank

FROM CTE r

OUTER APPLY(SELECT TOP 1 * FROM CTE r2

WHERE r2.EmailAddress = r.EmailAddress

AND r2.GroupResetFlag = 1

AND r2.CreatedAt <= r.CreatedAt

ORDER BY r2.CreatedAt DESC, r2.QuoteId DESC) ca

ORDER BY

r.EmailAddress

,r.CreatedAt;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537