Rob-350472 (4/15/2013)
What I'm trying to do is something I've not done before (perhaps this is why!), say you've created a temp table - a bit like my example - but you wanted to count how many times an item appeared in it over specific date ranges...I was thinking I could join to the table once to get 'all time'
then join again and have something along the lines of and Log_Date > dateadd(mm, -6, getdate()) to essentially perform the count on a subset and so on.
In the past I'd used separate temp tables based on the time horizons, so say 4 temp tables rather than attempting 4 joins, or I'd done a union.
I think the approach I'm tyring to adopt basically isn't going to work - I blame it on an office that's far too warm - can't think straight! (well that's my excuse and I'm sticking to it)
The arithmetic overflow makes sense now though I guess because the actual temp table has 659,965 rows so yeah the square of that is quite large!
Maybe easiest in that case to use subselects? I will give you a hand if you need it but I would need a bit more details first. It certainly can be done fairly easily.
Glad I was able to shed some light on what was going on at least. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/