Chop the last three digits off of calltime so that you're left with just the hour portion, which you can convert to an integer and do proper arithmetic on.  Please...

WHERE Direction IS NOT NULL

John

Before you change the application's connection string, do yourself a favour and create a DNS alias for the new server.  Call it SQLMyApplication or something that fits your organisation's connection...

COUNT doesn't need an ORDER BY in the OVER clause.  And as you and I have just learned, not only is it not needed, it's not permitted either.
[code...

What indexes do you have on table1, table2 and tbl_needs_ranking?

Have you considered something like this - doing the ranking and both inserts all in one operation?  Your transaction...

Last full backup nine months ago is not a good situation to be in.  It looks as if that may be the best you can do, though.  Restore that -...

Chris

Good spot.  I saw that when I looked in the execution plan.  What still puzzles me, though, is that my code runs about three times as fast with...

Arshad

Have you looked at the LAG function?  Why can you not use CTEs, incidentally?

John

Goodness, yes - I tried it with an on-the-fly table.  Mine slowed down significantly and yours sped up a lot.  I can't really explain that.

I changed the tally...

Chris

Yes, you appear to have changed my PARTITION BY clauses.  The first one should be PARTITION BY name, CHR; the second should be PARTITION BY...

Chris

Your code is certainly more compact, but I couldn't find any way to make it faster than mine.  I don't know whether that's to do with all the...

This should perform better than either a self-join or a recursive CTE.  The first CTE isn't part of the solution - I just used it because I don't have a...

You could drop the CROSS and change the WHERE to ON.  Same query, just avoiding the dreaded cross join syntax.

John