You might want to have a look at this blog. It will demonstrate an alternative solution that most probably will perform better than your current approach.

Do you use SQL Server 2000 (as indicated by the forum you posted in) or another version? Makes a big difference regarding the solution for the ginven task.

It depends.

Depending on the data structure the following code might return the same result:

SELECT sub.Count,F.*

FROM

Table F

INNER JOIN

...

I'm confident this link will help you getting started on the calendar table concept.

Before I post my solution: You did an EXCELLENT JOB describing the scenario and providing ready to use sample data! There's nothing better to ask for! GREAT!!

And here's what I...

There are some bets where one can almost always win....

First and most important question: are you sure you're using SQL Server 2000 and not 2005 or 2008 version? It's important because since SQL Server 2005 it's been a lot...

I aliased the tables to improve readability...

It seems like the statistics are out of date. If you compare estimated and actual number of rows, there's a big difference.

The other reason for the first query taking...

You're using the table name only in your join predicate: table1.acctnumber = table2.acctnumber

But those table names are interpreted as being local tables.

The easiest would be to assign an alias to...

I kindly ask you to read my latest reply just to be aware of the trouble you might run into when using wk or dw together with DATEPART()...

Since I don't have any table def and sample data this is just a guess. Close enough?

SELECT

UMMTR.DISTRICT,

UMMTR.RATETYPE,

TRNCD.DESCRIPT,

UMMTR.XSIZE,

UMSIZ.DESCRIPT AS SIZEDESC,

...

I strongly vote against using DATEPART(wk,...) or DATEPART(dw,...) since it depends on the setting of @@DATEFIRST, which in turn can be modified by a different language setting.

And all of...

Google is your friend in this case:

I searched for "The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020" and the first result...

Something like this?

SELECT COUNT(task_id) AS cnt,DATEPART(isowk,create_time) AS ISO_wk,YEAR(create_time) AS Yr

FROM Tasks

GROUP BY YEAR(create_time),DATEPART(isowk,create_time)

Since you didn't define the start of a week I jus assume you'll follow the ISO definition.

If not,...