title: SQL Server get customer with 7 consecutive transactions
body: <p>I am trying to write a query that would get the customers with 7 consecutive transactions given a list of CustomerKeys.</p>
  
  <p>I am currently doing a self join on Customer fact table that has 700 Million records in SQL Server 2008.</p>
  
  <p>This is is what I came up with but its taking a long time to run. I have an clustered index as (<code>CustomerKey, TranDateKey</code>)</p>
  
  <pre><code>SELECT 
      ct1.CustomerKey,ct1.TranDateKey
  FROM
      CustomerTransactionFact ct1
  INNER JOIN 
      #CRTCustomerList dl ON ct1.CustomerKey = dl.CustomerKey --temp table with customer list
  INNER JOIN 
      dbo.CustomerTransactionFact ct2 ON ct1.CustomerKey = ct2.CustomerKey -- Same Customer
                                      AND ct2.TranDateKey &gt;= ct1.TranDateKey 
                                      AND ct2.TranDateKey &lt;= CONVERT(VARCHAR(8), (dateadd(d, 6, ct1.TranDateTime), 112) -- Consecutive Transactions in the last 7 days
  WHERE  
      ct1.LogID &gt;= 82800000
      AND ct2.LogID &gt;= 82800000
      AND ct1.TranDateKey between dl.BeginTranDateKey and dl.EndTranDateKey
      AND ct2.TranDateKey between dl.BeginTranDateKey and dl.EndTranDateKey
  GROUP BY   
      ct1.CustomerKey,ct1.TranDateKey
  HAVING 
      COUNT(*) = 7
  </code></pre>
  
  <p>Please help make it more efficient. Is there a better way to write this query in 2008?</p>