See

http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/%5B/url%5D

This would be a starter

http://technet.microsoft.com/en-us/library/bb630290(SQL.100).aspx

Interbase can give you some clues

http://www.ibprovider.com/eng/documentation/odbc_escape_sequences_eng.html

No. FN makes your connection resort to ODBC level calls and use the WEEK function and calculate the week value for created column.

It will be faster to use DATEPART(WEEK, Created)...

DATEPART(WEEK, Col1)

The "blog topic" text is a link to the actual blog topic entry.

http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

With Peso 1 it takes about 458,426 milliseconds for all 1,000 ProcessCell set to same value

Give or take...

With Peso 3 it takes about 76 milliseconds.

With...

Turned out to be easier than I thought.

The "collapsed" date ranges are already sequenced 🙂

-- Get the collapsed date ranges

SELECTProcessCell,

MIN(DateFrom) AS DateFrom,

MAX(DateTo) AS DateTo

FROM#ProcessCellAllocation

GROUP BYProcessCell,

Seq

ORDER BYSeq

Thanks Jeff.

It sure is an interesting problem!

I tested with 100,000 date pairs and Peso 3 run in 900 ms. With 1,000,000 date pairs Peso 3 run in 2.2 seconds.

I will...

For 10,000 date pairs, I couldn't run Peso 1 and Michael in a timely fashion.

Here are however the results for Peso 2 and Peso 3

** Peso 2

Table 'Worktable'. Scan...

Here is a script for testing, Jeff.

Results for the three previous methods and a new method is included below

Peso 1 - Traditional t-sqlSQL Profiler - Rowcount 949, reads 43,512, CPU...

Or you can connect with a stream file object.

Also asked and answered here

http://www.sqlservercentral.com/Forums/Topic492872-8-5.aspx#bm498859

Also asked here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102718

7 times faster?

Not that bad for a LIKE '%' + Col1 + '%' query.

You are referring to this topic?

http://www.sqlservercentral.com/Forums/Topic317259-9-1.aspx

Yes, I wrote that just for testing output from yours faster functions, which initially had some flaws.

I am happy we worked them out and now...