There are two orders with an
orderdate=1997-09-05. One for employee=4 and one for employee=7. Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table. That totals to 228. Your query is returning all orders where the employeeid matches those two id's.
Alter your query to include a check for
FROM orders o
WHERE EXISTS (
FROM orders o2
WHERE o2.EmployeeID = o.EmployeeID
AND YEAR(o2.OrderDate) = 1997
AND MONTH(o2.OrderDate) = 9
AND DAY(o2.OrderDate) = 5
AND o2.OrderDate = o.OrderDate
Based on your example, you would get the same results by simplifying the query to:
select * from orders where orderdate = '1997-09-05'