|
|
The mission is to find a pair of latest invoices of
each sales person employed in Colombo ltd. All nessesary information
is stored in "Invoices" table.
Invoices:
| DocNo | DocDate | DocTotal | SlpCode | ... |
| 1 | 14/01/99 | 2375 | 11 | ... |
| 2 | 22/01/99 | 1291.5 | 11 | ... |
| 3 | 14/02/99 | 10200 | 10 | ... |
| 4 | 10/03/99 | 250 | 8 | ... |
We just need to identify two records with the latest
DocDate value per sales person represented by
SlpCode number.
First query (q_LastInvoice_First) is simple
it retrieves latest invoice record using subquery with MAX(DocDate):
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM Invoice AS a INNER JOIN
(SELECT SlpCode, MAX(DocDate) AS MaxDate
FROM Invoice GROUP BY SlpCode) AS b
ON (a.DocDate = b.MaxDate) AND (a.SlpCode = b.SlpCode);
|
It returns following results:
| SlpCode | DocNo | DocDate |
| 11 | 27 | 04/03/00 |
| 8 | 29 | 02/05/00 |
| 9 | 30 | 14/06/00 |
| 10 | 31 | 26/06/00 |
Next we'll define intermidiate query (q_LastInvoice_Tmp
) that will retrieve all invoices except those picked up by the first
query:
SELECT t1.SlpCode, t1.DocNo, t1.DocDate, t2.DocNo
FROM Invoice AS t1 LEFT JOIN
(SELECT a.SlpCode, a.DocNo, a.DocDate
FROM Invoice AS a INNER JOIN
(SELECT SlpCode, MAX(DocDate) AS MaxDate
FROM Invoice GROUP BY SlpCode) AS b
ON (a.DocDate = b.MaxDate) AND (a.SlpCode = b.SlpCode)) AS t2
ON t1.DocNo = t2.DocNo
WHERE t2.DocNo IS NULL;
|
After doing this it's easy to define last query (
q_LastInvoice_Second) that will retrieve invoice that is one before
the latest:
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM Invoice AS a INNER JOIN
(SELECT SlpCode, MAX(DocDate) AS MaxDate
FROM q_LastInvoice_Tmp GROUP BY SlpCode) AS b
ON (a.SlpCode = b.SlpCode) AND (a.DocDate = b.MaxDate);
|
Finaly we can execute two queries together using UNION:
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM
(SELECT SlpCode, DocNo, DocDate, 1 AS ord_no
FROM q_LastInvoice_First
UNION ALL
SELECT SlpCode, DocNo, DocDate, 2 AS ord_no
FROM q_LastInvoice_Second) AS a
ORDER BY a.SlpCode, a.ord_no;
|
And recive:
| SlpCode | DocNo | DocDate |
| 8 | 29 | 02/05/00 |
| 8 | 12 | 15/10/99 |
| 9 | 30 | 14/06/00 |
| 9 | 26 | 08/02/00 |
| 10 | 31 | 26/06/00 |
| 10 | 28 | 22/04/00 |
| 11 | 27 | 04/03/00 |
| 11 | 22 | 02/12/99 |
|