x

Creating a Temporary Table and then populating with multiple columns

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:

Proj   Prod   Value
P001     C    6,000
P001     C    7,500
P001     C    4,500
P002     A    4,000
P002     C    3,000
P003     W    1,000
P003     C    1,500
P004     C    4,000

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:

Proj   A No  A Ave     C No  C Ave      W No  W Ave
P001    0     0.00      3  6000.00       0     0.00
P002    1  4000.00      1  3000.00       0     0.00
P003    0     0.00      1  1500.00       1  1000.00
P004    0     0.00      1  4000.00       0     0.00

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:

DECLARE  @Project TABLE
( proj_num NVARCHAR(20)
, QNoC  int DEFAULT (0)
, QValC Decimal(10,2) DEFAULT(0.00))

INSERT INTO @Project
( proj_num )        
SELECT proj_num        
FROM Live_App.dbo.proj
WHERE ((Live_App.dbo.proj.proj_num >= 'P001')
AND    (Live_App.dbo.proj.proj_num <= 'P004'))
ORDER BY proj_num

UPDATE @Project
SET QNoC = (SELECT ISNULL(COUNT(Live_App.dbo.co.co_num),0)
FROM Live_App.dbo.co INNER JOIN @Project ON Live_App.dbo.co.charfld1 = proj_num
WHERE ((Live_App.dbo.proj.proj_num = Live_App.dbo.co.charfld1) -- Not sure if req or not
AND    (Live_App.dbo.co.charfld1 >= 'P001')
AND    (Live_App.dbo.co.charfld1 <= 'P004')
AND    (Live_App.dbo.co.type = 'E') --To only bring in Quotes, not orders (Type 'R')
AND    (Live_App.dbo.co.UF_CO012 = N'C'))) -- To bring in Prod Type 'C's to test

UPDATE @Project
SET QValC = (SELECT ISNULL(AVG(Live_App.dbo.co.UfCOSubTotal),0)
FROM Live_App.dbo.co INNER JOIN @Project ON Live_App.dbo.co.charfld1 = proj_num
WHERE ((proj_num = Live_App.dbo.co.charfld1) -- Not sure if required or not.
AND    (Live_App.dbo.co.charfld1 >= 'P001')
AND    (Live_App.dbo.co.charfld1 <= 'P004')
AND    (Live_App.dbo.co.type = 'E') --To only bring in Quotes not orders (Type 'R')
AND    (Live_App.dbo.co.UF_CO012 = N'C'))) -- To bring in Prod Type 'C's to test

SELECT proj_num
, QNoC
, QValC
FROM @Project

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:

Proj   C No      C Ave
P001    6      4416.66
P002    6      4416.66
P003    6      4416.66
P004    6      4416.66
Any help on how to get the data coming in correctly would be appreciated.
more ▼

asked Jun 21, 2011 at 07:24 AM in Default

Andy Hornby gravatar image

Andy Hornby
43 5 5 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

declare @projects table
(
    Project varchar(4),
    Product char(1),
    Value decimal(10,2)
);

insert into @projects
select 'P001',  'C',  6000 union all
select 'P001',  'C',  7500 union all
select 'P001',  'C',  4500 union all
select 'P002',  'A',  4000 union all
select 'P002',  'C',  3000 union all
select 'P003',  'W',  1000 union all
select 'P003',  'C',  1500 union all
select 'P004',  'C',  4000;

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.

select 
    Project,
    Product,
    COUNT(*) as Records,
    AVG(Value) as AverageValue
from 
    @projects
group by
    Project,
    Product;

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.

with averages as
(   
    select
       [Project],
       pvt.[A],
       pvt.[C],
       pvt.[W]
    from
       (
         select Project, Product, Value
         from @projects
       ) p
       PIVOT
       (
         AVG(Value)
         for [Product]
          in ([A],[C],[W])
       ) as pvt
),
counts as
(
    select
       [Project],
       pvt.[A],
       pvt.[C],
       pvt.[W]
    from
       (
         select Project, Product, Value
         from @projects
       ) p
       PIVOT
       (
         COUNT(Value)
         for [Product]
          in ([A],[C],[W])
       ) as pvt
)
select
    a.Project,
    c.A as ACount,
    coalesce(a.A, 0) as AAverage,
    c.C as CCount,
    coalesce(a.C, 0) as CAverage,
    c.W as WCount,
    coalesce(a.W, 0) as WAverage
from
    averages a
    inner join counts c on a.Project = c.Project;

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][2]. They're a little tricky to debug but can do the trick.

[2]: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
more ▼

answered Jun 21, 2011 at 08:02 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Kevin. Thanks for your swift reply. Just wanted to check before I work through this, can I just clarify the data that I'm actually working with, as the sample data might have made it seem more simple. In my actual version, the number of projects I have declared are between 2 variables (@FromProj and @ToProj), as I will be looking at a range of a thousand or so from a table containing about 40,000 Projects. The number of Products Types is currently 14, so my results returned into Excel would be at least 1,000 rows and 28 columns. Is that okay with the code you suggest ? Thanks Andy
Jun 21, 2011 at 08:21 AM Andy Hornby
Sorry, also forgot to say that I want to return all Project numbers within the range, even those that don't have any records (a row of zero counts and zero averages returned).
Jun 21, 2011 at 08:31 AM Andy Hornby

Performance should be OK with the number of records you mention, as long as your table is indexed appropriately. The pivot won't break down that quickly. 14 product types may be close to the point where you would want to use a dynamic pivot, perhaps, but as long as product types are stable, a normal pivot would work, too, so it becomes a matter of easy to read versus less typing.

All projects would be aggregated, so as long as there is some reference to the project, it would appear.. I added in a P005 with NULL for Product and Value to the sample data, and it was added with the expected results. So as long as there is some listing of the project, it will appear.
Jun 21, 2011 at 09:15 AM Kevin Feasel
Kevin, Sorry for the delay in replying, have been off doing other (non-SQL) stuff and on holiday. I did the code using the pivot method, as I only had 14 defined products and it worked a treat, thank you, this is a few steps up from any code I've done previously! The only slight downside is the totals (using the COALESCE function) I would ideally like to round to 2 decimals, but have tried putting the ROUND function in without success. Any ideas how to combine the ROUND function with the COALESCE function ? Thanks again for this. Andy
Jul 28, 2011 at 01:36 AM Andy Hornby

You should be able to do it right inside the coalesce:

coalesce(round(a.C, 2), 0) as CAverage,

Coalesce basically become a case statement where it searches for the first thing which is not null. Then, each bit inside the coalesce can call its own function: in this case, we round a.C to two decimal points.
Jul 28, 2011 at 04:23 AM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x2

asked: Jun 21, 2011 at 07:24 AM

Seen: 4049 times

Last Updated: Jun 21, 2011 at 07:52 AM