|
Setting up Picnic Budget for All Departments
with a Complex UPDATE
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP
June 4, 2005
We want to set up budgets for each department to be used for
picnics based on the number of employees in that department. We
could do it in steps using temporary tables or table variables,
however, to demonstrate the power of Transact-SQL, we do it in
one complex UPDATE statement using CASE and GROUP BY.
First we have to add a new column to the Department table:
USE AdventureWorks
ALTER TABLE HumanResources.Department ADD PicnicBudget INT not
NULL DEFAULT 0
GO
We are now ready for the huge UPDATE. Since we cannot use GROUP
BY directly in an update, we use it a separate SELECT and alias
it as a table.
UPDATE HumanResources.Department
SET PicnicBudget =
(CASE
WHEN (DG.DeptEmployees > 0 AND DG.DeptEmployees
< 4) THEN 100
WHEN (DG.DeptEmployees >= 4 AND DG.DeptEmployees
< 8) THEN 200
WHEN (DG.DeptEmployees >= 8 AND DG.DeptEmployees
< 20) THEN 300
WHEN (DG.DeptEmployees >= 20 AND DG.DeptEmployees
< 50) THEN 500
ELSE 1000
END)
FROM HumanResources.Department DE, (SELECT D.DepartmentID, COUNT(*)
AS DeptEmployees
FROM HumanResources.Department D JOIN
HumanResources.Employee E
ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentID ) DG
WHERE DE.DepartmentID = DG.DepartmentID
We use the following SELECT to get the results:
SELECT GroupName, Name, '$'+convert(char,PicnicBudget)
FROM HumanResources.Department
ORDER BY GroupName, Name
Here are the picnic budgets:
Executive General and Administration Executive $100
Executive General and Administration Facilities and Maintenance
$200
Executive General and Administration Finance $300
Executive General and Administration Human Resources $200
Executive General and Administration Information Services $300
Inventory Management Purchasing $300
Inventory Management Shipping and Receiving $200
Manufacturing Production $1000
Manufacturing Production Control $200
Quality Assurance Document Control $200
Quality Assurance Quality Assurance $200
Research and Development Engineering $200
Research and Development Research and Development $200
Research and Development Tool Design $200
Sales and Marketing Marketing $300
Sales and Marketing Sales $300
|
