Hi Reader,

I'm fairly new to SQL and am struggling with this
query.  Can someone help me out?

I have three tables, Employee, Sale and EmployeeSale.
performance by calculating the value of sales each of my
staff has made in a qtr and the average no of sales made
in a qtr by all my staff.

I then want to be able to calculate the average no of sales
each of my staff has done in over a year.  See below for
sample output.

output as such
qtr   year    mick   sally   alice   avg
1     2002    6      0          4         3.3
2     2002    6      3          4         4.3
3     2002    1      4          2
4     2002    2      1          1
avg to date 3.75   2          2.75

Here are my table definitions

CREATE TABLE Employee
(
   EmployeeID int PRIMARY KEY IDENTITY(1000, 1),
   EmployeeName varchar(50) NOT NULL
)

CREATE TABLE Sale
(
   SaleID int PRIMARY KEY IDENTITY(1000, 1),
   SaleName varchar(30)
)

CREATE TABLE EmployeeSale
(
   SaleID int NOT NULL REFERENCES Sale(SaleID),
   EmployeeID int NOT NULL REFERENCES Employee(EmployeeID),
   PRIMARY KEY (SaleID, EmployeeID),
   YearSold char(4) CHECK(YearSold LIKE '[1-9][0-9][0-9][0-9]'),
   PeriodSold tinyint CHECK(PeriodSold BETWEEN 1 AND 4),
   ValueOfSale smallint
)

Please help!

Kev

(kevin_james1***@yahoo.co.uk) writes:
Show quoteHide quoteHere is a query that given your tables may give you what you want:

   SELECT PeriodSOld AS ES.qtr, ES.Year.
          SUM(CASE WHEN E.EmployeeName = 'mick'
                   THEN ES.ValueOfSale END) AS mick,
          SUM(CASE WHEN E.EmployeeName = 'sally'
                   THEN ES.ValueOfSale END) AS sally,
          SUM(CASE WHEN E.EmployeeName = 'alice'
                   THEN ES.ValueOfSale END) AS alice,
          cast(SUM(ES.ValueOfSale / 3.0) as decimal(9,1)) AS avg
   FROM   EmployeeSale ES
   JOIN   Employees E ON ES.EmployeeID = E.EmployeeIF
   GROUP  BY ES.qtr, ES.Year
   COMPUTE AVG(SUM(CASE WHEN E.EmployeeName = 'mick'
                   THEN ES.ValueOfSale END)),
           AVG(SUM(CASE WHEN E.EmployeeName = 'sally'
                   THEN ES.ValueOfSale END)),
            AVG(SUM(CASE WHEN E.EmployeeName = 'alias'
                   THEN ES.ValueOfSale END))


But there are several problems with it. The COMPUTE clause at the
bottom is strongly deprecated, and should not be used for
production code. I can understand that you want the rollup, but it's
usually best done client-side.

As you can see, the query has hard-coded employee names. I strongly
suspect that you have more than three employees. But in SQL a query
always return a known set of columns. This can be worked around by
composing the query with dynamic SQL, but that is no fun at all.
You may prefer to use a more normal query, where you also group on
employee, and perform the transpose client-side.

You may also be interested in checking out RAC, which is a third-party
tool which is good at this kind of things, http://www.rac4sql.net/.

Left Outer Join and Where Clause
finding missing dates in a sequence
Customer specific price matrix design (Long)
Update Query question.
Need help optimizing a stored procedure - SQL2k