Question:
Assignment: "Show first and last names of the employees as well as the count of their orders shipped after required date during the year 1997 (use left join) ")
At the moment I have a request like this:
SELECT
FirstName,
LastName,
COUNT(OrderID) as Count_of_Orders
FROM Orders
LEFT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
WHERE ShippedDate BETWEEN RAND(ShippedDate)
AND '1997-12-31'
AND DATEPART(YY,ShippedDate) = 1997
GROUP BY FirstName, LastName
But it gives an error: "Invalid implicit conversion of datetime data type to int data type, table" Orders ", column" ShippedDate ". Use the CONVERT function to execute this query." I've tried everything, but I can't convert the data correctly
Answer:
I offer my solution.
The condition says "orders shipped after required date" , that is, delivered after the required date!
SELECT FirstName, LastName, COUNT(OrderID) as Count_of_Orders
FROM Orders LEFT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE DATEPART(YY,ShippedDate)=1997 AND ShippedDate > RequiredDate
Group by FirstName, LastName