title: Set-based alternative to loop in SQL Server
body: <p>I know that there are several posts about how BAD it is to try to loop in SQL Server in a stored procedure.  But I haven't quite found what I am trying to do.  We are using data connectivity that can be linked internally directly into excel.  </p>
  
  <p>I have seen some posts where a few people have said they could convert most loops to a standard query.  But for the life of me I am having trouble with this one.  </p>
  
  <p>I need all <code>custID</code>s who have orders right before an event of type 38,40.  But only get them if there is no other order between the event and the order in the first query.</p>
  
  <p>So there are 3 parts.  I first query for all orders (orders table) based on a time frame into a temporary table.  </p>
  
  <pre><code>Select into temp1 odate, custId from orders where odate&gt;'5/1/12'
  </code></pre>
  
  <p>Then I could use the temp table to inner join on the secondary table to get a customer event (LogEvent table) that may have occurred some time in the past prior to the current order.  </p>
  
  <pre><code>Select into temp2 eventdate, temp1.custID from LogEvent inner join temp1 on 
  temp1.custID=LogEvent.custID where EventType in (38,40) and temp1.odate&gt;eventdate
  order by eventdate desc
  </code></pre>
  
  <p>The problem here is that the queries I am trying to run will return all rows for each of the customers from the first query where I only want the latest for each customer.  So this is where on the client side I would loop to only get one Event instead of all the old ones.  But as all the query has to run inside of Excel I can't really loop client side.</p>
  
  <p>The third step then could use the results from the second query to make check if the event occurred between most current order and any previous order.  I only want the data where the event precedes the order and no other orders are in between.</p>
  
  <pre><code>Select ordernum, shopcart.custID from shopcart right outer join temp2 on 
  shopcart.custID=temp2.custID where shopcart.odate &gt;= temp2.eventdate and
  ordernum is null
  </code></pre>
  
  <p>Is there a way to simplify this and make it set-based to run in SQL Server instead of some kind of loop that I is perform at the client?</p>