TSQL -> Query that retrieves last invoice made by sales-person

Suppose that we have to find out the the last invoice made by every sales-person of Colombo-Ltd.

USE [colombo]
GO

/*
this select retrives last invoice date for sales-person number 8
*/

SELECT MAX(a.DocDate) FROM dbo.Invoice AS a WHERE a.SlpCode = 8

/*
this select retrives last invoice number for sales-person number 8 in the last date. the point is that sales-person can make several invoices per day
*/

SELECT MAX(b.DocNo)
FROM dbo.Invoice AS b
WHERE b.SlpCode = 8 AND b.DocDate =
   (SELECT MAX(a.DocDate) FROM dbo.Invoice AS a WHERE a.SlpCode = 8)

/*
now we'll add one more level and will ask for the list of all sales-persons instead of specific one. sales-person number is equal to EmployeeId in Employees table
*/

SELECT c.DocNo AS InvoiceNo, c.DocDate, c.CustomerName,
RTrim(e.FirstName) + ' ' + RTrim(e.LastName) AS SalesPerson
FROM dbo.Invoice AS c INNER JOIN dbo.Employees AS e
ON c.SlpCode = e.EmpNo
WHERE c.DocNo =
(SELECT MAX(b.DocNo)
  FROM dbo.Invoice AS b
  WHERE b.SlpCode = c.SlpCode AND b.DocDate =
   (SELECT MAX(a.DocDate) FROM dbo.Invoice AS a
    WHERE a.SlpCode = c.SlpCode))


Query results will look like this:
InvoiceNoDocDateCustomerNameSalesPerson
272000-03-04Antonella Musentahgina sohn
292000-05-02Valentina Mazepadona fiber
302000-06-14Enrice Duranceleonardo rota
312000-06-26Paris Ottonaiva pesh



sqlexamples.info