SQL Question 15

Problem

  • USE Northwind
  • Write a script that will display the last name and count of orders for employees over 60. Use an IF statement in your script to test whether an employee is over 60 and if so then print the information. Also use a While statement to loop through all employees
  • Don’t hard code your loop. In other words your program should work whether there are 8 employees or 10.

Code

Use Northwind
Declare @age int
Declare @RowNum int
Declare @employeeName varchar(10)
Declare @Num_Sold numeric(10,2)

SET @RowNum = 0
-- loop through all employees
WHILE @RowNum < (SELECT COUNT(*) from employees)

BEGIN
SET @RowNum = @RowNum + 1
SET @age = (SELECT DATEDIFF(yy,birthdate, GETDATE()) FROM employees e WHERE e.employeeid =@rownum)
IF @age >= 60
BEGIN
SELECT @employeeName = lastname, @Num_Sold = Count(orderid)
FROM employees e JOIN Orders o ON e.employeeid = o.employeeid
WHERE e.employeeid =@rownum
GROUP BY lastname
PRINT CAST(@RowNum as char(2)) + ' ' + @employeeName + ' ' + CONVERT(varchar,@Num_Sold)
END
END

Result

Question 15 Result

Question 15 Result

Notes

This script uses an IF statement to test if the age of an employee is over 60. The @age variable is assigned the value of the DATEDIFF between today's date (GETDATE()) and the employee's birthdate. Variables @employeeName is set equal to the lastname of the employee and @Num_Sold to the COUNT(orderID). In the PRINT statement, the @RowNum is CAST as CHAR(2) and concatenated with the@employeeName and @Num_Sold.

Comments are closed.