Frankly with these approaches, you can use any table...

On the men/women thing, do the file names contain a substring that identifies the gender contained within the file? And if so, is that substring consistent over time, so that...

Ok, to be clear, let's address the For Loop issue first.

I think you want a For Each Loop Container, not a For Loop Container. A For Loop is best for...

I'm a little fuzzy on your needs, but I think you may be looking to use a For Each Loop Container. An FELC is a task container that allows you...

I now have a new favorite. :blush: The Itzek-style cross join. Begs the question, why does MS place the emphasis they do on cursors and rCTEs in certification. A bit...

Excellent. That's why you guys are paid more than me. I'm digging into the article. Thanks for the pointer.

I have a new favorite method, courtesy of Mike Powell at

http://stackoverflow.com/questions/58429/sql-set-based-range

No dependencies, backward compatible w/ 2005.

DECLARE @MyDates TABLE(d DATETIME NOT NULL);

WITH CTE AS (

SELECT1 AS n

UNION ALL

SELECTn + 1 AS...

This may seem a little silly, kind of a tally-table-without-a-tally-table solution...

INSERT INTO @MyDates (d)

SELECT TOP 60 DATEADD(day, -1 * (ROW_NUMBER() OVER (ORDER BY name)), getdate())

FROM Master.dbo.SysColumns;

Thanks, ColdCoffee. I owe drew.allen for expanding my thinking on using APPLY.

And good points about the assumptions. I guess a false assumption on either of those two points could complicate...

How's this:

SELECTB.[To] AS IdleFrom

, A.[From] AS IdleTo

FROM A

OUTER APPLY(

SELECTTOP 1 [To]

FROM

WHERE[To] < A.[From]

ORDER BY[To] DESC) B

WHEREB.[To] IS NOT NULL;

Sanz,

You might do a little better by providing more details in your post. Many of the gurus on this site (I'm absolutely not one of them) will not bother to...

Based on current indexing, execution time on all three is very close. The two suggestions show a higher cost, but I can see where some index tuning may make them...

If ROW_NUMBER is used instead of RANK, won't it produce unpredictable results in cases when there are ties? In R.P.'s data sample, rows 2 through 7 are interpreted by ROW_NUMBER...

Thanks, Drew. So you're suggesting something like this?

WITH Rnk AS (SELECT Key, Date, Status

, RANK() OVER(PARTITION BY Key ORDER BY Date DESC) AS Ranking

FROM table

)

SELECT Key, Date, Status, Ranking

FROM Rnk

WHERE...

I'm not exactly clear on what you're after, but take a look at the code below. I make no claims about it's performance, other than to strongly suggest that you...