We have a number of Project Numbers and each of those might have 1 or more quotes against it and each quote will have a value and a Product Group. A sample of data is as follows:
The Projects (field called 'proj_num') are held in a table called 'proj' and the Quotes (field called 'co_num') are held in the 'co' table. What I want to achieve is a list of all the Project Numbers for any given range (Column A), then for each Product the number of Quotes and the Average of the quotes, so based on the above data, the results I would want to be returned would be as follows:
So far (and I'm only trying to get it working for the Product 'C' first - which would return only 3 columns of data), the code I have is as follows:
The data is returning, but it is giving me the number of and the average value for all the projects against each project, so based on the sample data, the data my code would return would be as follows:
Any help on how to get the data coming in correctly would be appreciated.
I'm not sure that you actually need temporary tables and multi-update logic here. It looks like you should be able to pull it off in one query, given the data set that you provided.
First, the data set:
This first query creates a grouped count and average. If you are doing something like populating a matrix in Reporting Services, this is probably the easiest option.
If you have a fixed, determinate number of products, you can use the PIVOT statement. You can only pivot on one aggregation, so I needed two pivots to get the counts and the averages. Because of this, I wrapped them in common table expressions to make it easier to read.
If you do not have a fixed set of products and you need to do this within T-SQL, you could look at [dynamic pivots]. They're a little tricky to debug but can do the trick.: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx