Hi Julian,

if I understand you correctly, what you need is a full join.

SELECT T1.weekday, T1.weektype, count(T1.orders) As 'OrderCount',sum(T1.CollectValue) as 'CollectValue', T5.CancelCount, T5.CancelValue

INTO #tempv

FROM #temp0 T1

FULL JOIN -- Changed to full...

The Events and Errors Message Center, located at http://www.microsoft.com/technet/support/ee/ee_advanced.aspx, is the primary source of information on error messages for SQL Server 2005 and improves the customer experience when searching for...

Florian Reischl (5/24/2009)

:w00t:

I didn't see the group by. Really. So I...

heh-he... and i wrote the group by solution in me first post as well as the distinct 😉

Have been thinking about dynamic custom data types via CLR for user defined...

Sorry Flo,

shouldn't have left the distinct query in the middle of my solution!

For reference it finally finished running after 23:53... indexed GROUP BY took 00:10.

The CLR route sounds promising,...

Hi Wayne,

Does your table have to be a variable, I had a go with a bigger data set and got really bad results on it, it got a lot...

Ok Jeff, again thanks a lot for your time and patience, turns out your article http://www.sqlservercentral.com/articles/cross+tab/65048/ and subsequent discussions answered all the queries I had — must spend a little...

Thanks for bringing some clarity here Jeff, I think I may have confused things by turning data in earlier post into a temporary table!

Many thanks for your post; I had...

This is something I have thought about myself, from time to time, and am interested to see if it is possible in sql. Pretty green myself, but it seems...

Cheers Paul, thanks for your help 🙂

Thanks Paul, I did consider that, but I thought there was some mileage in maintaining the data's structure as supplied to me, rather than post-normalising (?) the data.

Perhaps I should...

Something I did with maintaining revisions of important data was on every update to push the entire record to a backup table with exactly the same structure. With UserID...

I can't believe there is no mention of Son of Rambo... charming, warm, funny (laugh out loud lots) thoroughly recommended to all.

Do yourself a favour and get the DVD!