USE [Northwind]
GO

/****** Object:  StoredProcedure [dbo].[EmployeeOfTheMonth]    Script Date: 1/20/2023 9:53:07 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EmployeeOfTheMonth]
(
    @p_Year INT,
    @p_Month NVARCHAR(10)
)
AS
 -- employee(s) who has/have processed the maximum orders in a given month and year.
BEGIN
    SELECT 
	 EmployeeID
	,LastName
	,FirstName
	,FORMAT (HireDate, 'dd-MM-yy') AS DateHired
	,HomePhone
	,Extension 
	FROM Employees WHERE EmployeeID IN
    (
        SELECT EmployeeID FROM
        (
            SELECT top 1 EmployeeID, COUNT(OrderID) TotalOrders FROM Orders
            WHERE YEAR(OrderDate)=@p_Year AND DATENAME(MONTH,OrderDate)=@p_Month
            GROUP BY EmployeeID
            ORDER BY TotalOrders DESC
        ) AS EmployeeOfTheMonth
    )
END
GO