/*
Create a report showing the employee firstname and lastname,
a "NumOrders" column with a count of the orders taken, and a 
conditional column called "Shipped" that displays "On Time" if 
the order shipped on time and "Late" if the order shipped late.
Group records by employee firstname and lastname and then by the 
"Shipped" status. Order by employee lastname, then by firstname, 
and then descending by number of orders.
*/

SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders,
    (CASE
        WHEN o.ShippedDate < o.RequiredDate
            THEN 'On Time'
            ELSE 'Late'
            END)
        AS Shipped
FROM Orders o
    JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
GROUP BY e.FirstName, e.LastName,
    (CASE
        WHEN o.ShippedDate < o.RequiredDate
            THEN 'On Time'
            ELSE 'Late'
            END)
ORDER BY e.LastName, e.FirstName, NumOrders DESC;