All orders of the last day of the month for each employee

Question

Using the Northwind database on Microsoft SQL Server, I'm trying to find the last day of each month on which each employee has placed an order, as well as all the orders on that day by that employee.

for example: 1996-07-17 was the last day of month when employee 1 has placed an order. I want all the orders by employee 1 from this day.

select EmployeeID, max(convert(date, orderdate)) as LastDay
from northwind.dbo.Orders
group by YEAR(OrderDate),MONTH(OrderDate),EmployeeID
order by EmployeeID,LastDay;

This query returns the last days of months for each employee, but I couldn't get the orders.

Answer 1

Solution using a CTE. Since 2 was the max number of orders any employee placed on the last day of the month we can get a compact result set using STRING_AGG.

WITH CTE as 
(  
SELECT EmployeeID, max(convert(date, orderdate)) as LastDay
FROM Orders
GROUP BY YEAR(OrderDate),MONTH(OrderDate),EmployeeID
)
SELECT c.EmployeeID, 
       c.LastDay as LastDayOFMonth,
       Count(*) as [Order Count], 
       STRING_AGG(o.orderID,',') as [Orders] 
FROM ORDERS o
INNER JOIN CTE c on c.EmployeeID=o.EmployeeID
                AND c.LastDay=convert(date, o.orderdate)
GROUP BY c.EmployeeID, c.LastDay
ORDER BY c.EmployeeID, c.LastDay

fiddle

EmployeeID	LastDayOFMonth	Order Count	Orders
1	1998-04-21	2	11038,11039

1 example of 192 rows returned