|
|
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:
| InvoiceNo | DocDate | CustomerName | SalesPerson |
| 27 | 2000-03-04 | Antonella Musentah | gina sohn |
| 29 | 2000-05-02 | Valentina Mazepa | dona fiber |
| 30 | 2000-06-14 | Enrice Durance | leonardo rota |
| 31 | 2000-06-26 | Paris Otton | aiva pesh |
|