Hi,

I got this working a lot faster (registering zero seconds :w00t:) by getting rid of the dateadd datediff out of the join statement.

The report query now looks like:

SELECT

...

Also,

here's the script to populate the temp test table with arrival dates sans time component.

INSERT INTO #Stay (GuestID, ArrivalDate, TotalRevenue)

SELECT TOP 2000000

NEWID(),

DATEADD(DD,...

Ah, i see where the confusion was arising, you added a day, instead of a month in the less than part of each join condition:

So report query with your join...

Thanks for the reply Wayne,

Arrival date has no time associated with it on live data.

However, not sure what you mean with regards the join?

Checking the report query with my join...

Brilliant Jeff, thanks a million!

Hi Premal,

the following will give you the syntax of the create table http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

If you are having a particular problem using this command re-post detailing your issue.

Regards

The only successful means I have come across is creating an ordering column, example can be found at http://blog.beyondrelational.com/2008/10/ordering-hierarchical-data.html

Please note that you will get incorrect results if you have more...

Thought you guys might like this one: http://thedailywtf.com/Articles/Slightly-OverSQLd.aspx

Me too!

Thanks for the great article.

A cross-tab query as Ian has provided is the way to tackle this problem, you'll just have to get your group by correct so that the rows of you results...

Hi,

sorry, but I'm about to leave an OP hanging... am trying to get packed to go on holiday, and started into a avg/aggregate/median problem I haven't got a mission of...

If you can post some sample data from your sub query:

select week=DATEADD(wk, DATEDIFF(wk, 7, calldate), 7),

count(I3_RowID) as total, 1 As Lead,

From

test

where

(CallDate between '10/1/09' and '10/22/09')

and (reason<>'deleted' or reason<>'wrongparty' or reason<>'Success'...

Hi,

this is really good resource: http://www.sqlservercentral.com/articles/T-SQL/62867/

Hi, you may find the following useful: http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

Hi,

I used this to get rid of all £ signs out of data, you should be able to revise it for your purposes.

DECLARE @TableName VARCHAR(255)

SELECT @TableName = 'TableName'

DECLARE