/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/

SELECT c.CustomerID, c.CompanyName, e.EmployeeID AS EmpID,
       e.FirstName, e.LastName, OrderID,
    (CASE
        WHEN ShippedDate < RequiredDate
            THEN 'On Time'
            ELSE 'Late'
            END) AS Shipped
FROM Orders o
    JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
    JOIN Customers c ON (c.CustomerID = o.CustomerID)
ORDER BY Shipped;