Since you didn't follow etiquette by providing DDL and insert scripts for your sample, I used the AdventureWorks database. If you want with ties, you need to use the...

If you have a business that is 24/7, such as a website, no matter what time you run your reports at the end of the month, you risk missing some...

John Rowan (8/27/2009)

---

I did not say to not use a CTE. Its just an alternative way of coding the same solution. As far as ease-of-reading goes, they're almost...

But that solution is essentially the same as the CTE and the CTE is so much easier to read. What do you gain by...

I would modify your code to add a table number to guarantee that they are interleaved.

WITH CTE AS

(

SELECT *, 1 AS tableNum, ROW_NUMBER() OVER (ORDER BY test) AS rowNum FROM...

The following works with both multiple start and stop times. It may not be the exact results you want for multiple stop times.

WITH CTE AS

(

SELECT [action], [time], ROW_NUMBER() OVER...

That's not how SQL logic works. SQL returns ROWS that meet the given criteria, but your criteria isn't satisfied by a single row. You would need multiple rows...

I used AdventureworksDW, which only has four fiscal years of data, so I had to cheat on some of the numbers, but here is what I did. There may...

David's solution is correct given the sample formats, but if the formatting is not consistent, you can modify it slightly to give the correct results. For example where there...

Yes, but there is an obvious error in...

I don't see why you're even using a cursor in the first place. A cursor is horribly inefficient. You're just not noticing it, because there are so few...

There are certainly some similarities, but there are also differences. The big difference is that a correlated subquery can generally only return one value (one row with one column)...

First, I believe that this syntax is being deprecated in favor of using explicit JOINs.

Try...

Exactly what are you testing for? Accuracy? Efficiency? Legibility? Something else?

Drew

Is your data representative of the actual data? Your data only has one value for each date...