CODEWITHSUNDEEP
sqlsql-servernorthwind
graychar
graychar

Reputation: 3

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

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.

Upvotes: 0

Views: 88

Answers (2)

Bart McEndree
Bart McEndree

Reputation: 3293

This solution uses DENSE_RANK and is more efficient

WITH CTE as 
(  
SELECT o.*,
       DENSE_RANK() OVER (PARTITION BY YEAR(OrderDate),MONTH(OrderDate),EmployeeID ORDER BY orderdate DESC) AS rn
FROM Orders o
) 
SELECT c.EmployeeID, 
       c.orderdate as LastDayOFMonth,
       c.orderID
FROM cte c
WHERE c.rn=1  
ORDER BY c.EmployeeID, c.orderdate

fiddle

fiddle with cost comparison

EmployeeID LastDayOfMonth OrderID
1 1996-07-17 10258
1 1996-08-29 10293
1 1996-09-27 10316
1 1996-10-29 10340
1 1996-11-26 10364
1 1996-12-27 10396
1 1997-01-06 10405
1 1997-02-28 10461
1 1997-03-26 10486
1 1997-04-16 10508
1 1997-05-23 10546
1 1997-06-25 10579
1 1997-07-31 10616
1 1997-08-13 10630
1 1997-09-24 10680
1 1997-10-27 10718
1 1997-11-19 10746
1 1997-12-26 10800
1 1998-01-29 10859
1 1998-02-27 10916
1 1998-03-30 10984
1 1998-04-21 11038
1 1998-04-21 11039

Upvotes: 0

Bart McEndree
Bart McEndree

Reputation: 3293

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

Upvotes: 1

Related Questions