If you're stuck with the stored procedure as it is, you can insert the results into a temp table and select from that.

John

Yes, I think that must have been the aspect that I ran out of time on.  The reason I included the CTE that reverses the order was so that rows...

Alex

Yes, that's right.  Imagine you have five dishes to wash.  You'd do them yourself because it's quicker, even if you had friends sitting around doing nothing.  Now imagine...

This should get you started.  It's not completely as you asked, but I ran out of time.  If you've never used windowing functions then you'll need to read about them...

Seriously - can you not change the compatibility level to 2012?  It's just too difficult if you can't use windowing functions.

John

It means, for example, that on 27th April, you don't know whether the COMP on the N-CO came first.  IN such circumstances, depending on how the query is written, SQL...

Yes, I get the gist of what you're trying to do, but you still need a tiebreaker for rows that have the same CompletedDate.  Why not include the time portion...

To solve this, as far as I can see, we need to using windowing functions, which rely on an ORDER BY clause.  My question is: if there's a tie on...

So it's not hung as such, it's just taking longer?  What has changed?  Do you have more data than you had before?  Do you maintain your statistics and indexes with...

Where you have two events with the same CompletedDate, how do you determine which one comes first?

John

You've been around long enough to know that you haven't provided nearly enough information.  What does the job do?  What makes you think it's hung?  How long does it normally...

SUM(TCost + OffCost) OVER (PARTITION BY [Dept#])

John

You mean from SELECT @retval?  It's because you have no @retval in your code.  I would remove those second and third parameters - they don't serve any useful purpose.