|
|
The mission is to analize sales information coming from two
tables: Invoices and Invoices-Lines. First table include information
from Invoice header (like Issue Date, Total Sum, CustomerID ...), Invoice Lines
table include list of Items which was selled (Item Name, Price, Quantity ...).
Table "Invoices" is referenced to table "InvLines" by column "DocKey". Type
of reference is one-to-many: one record in "Invoices" reference to many records
in "InvLines".
Invoices:
| DocNo | DocKey | DocDate | DocTotal | CustomerName | SlpCode | ... |
| 1 | 100 | 14/01/99 | 2375 | Angelina Alba | 11 | ... |
| 2 | 101 | 22/01/99 | 1291.5 | Pestiana Oliviera | 11 | ... |
| 3 | 102 | 14/02/99 | 10200 | Enrice Durance | 10 | ... |
| 4 | 103 | 10/03/99 | 250 | Jessica Simpson | 8 | ... |
InvLines:
| DocKey | ItemCode | ItemName | Qnt | ... |
| 100 | 0036876 | Memorex 8.5Gb | 20 | ... |
| 100 | 77003 | Panasonic DMR-E | 3 | ... |
| 101 | 5003258 | Philips DVP642 | 45 | ... |
Four sales reports:
1. Select invoices that sold any kind of "Sony" products :
SELECT a.DocNo, a.CustomerID, a.DocTotal, a.DocDate
FROM Invoice AS a INNER JOIN InvLines AS b ON a.DocKey=b.DocKey
WHERE b.ItemName LIKE '*Sony*';
/* As you see join is made here by "DocKey" field */
|
2. Now your boss want to get Montly Sales of "Sony" products in 1999:
SELECT Month(a.DocDate) AS The_Month, SUM(a.DocTotal) AS Income
FROM (SELECT a.DocNo, a.CustomerID, a.DocTotal, a.DocDate
FROM Invoice AS a INNER JOIN InvLines AS b ON a.DocKey = b.DocKey
WHERE Year(a.DocDate) = 1999 ) AS a
GROUP BY Month(a.DocDate);
/* We used here subquery inside brackets. Then groupe result set by Month */
|
The result will be like this:
| The_Month | Income |
| 1 | 7993.1 |
| 2 | 20775 |
| 3 | 750 |
| ... | ... |
3. Now suppose that the company launched two advertizing projects:
1 - global TV advertizing
2 - community radio
Your management wants to monitor the eficiency of these two projects.
Every invoice issued was attributed to relevat project; field "projectid".
Report have to retrieve monthly sales of 1999 separated by project:
SELECT b.project_title, Month(a.DocDate) AS The_Month,
SUM(a.DocTotal) AS Income
FROM (SELECT a.projectid, a.DocNo, a.CustomerID,
a.DocTotal, a.DocDate
FROM Invoice AS a INNER JOIN InvLines AS b ON a.DocKey = b.DocKey
WHERE Year(a.DocDate) = 1999) AS a INNER JOIN AdvProjects AS b
ON a.projectid = b.projectid
GROUP BY b.project_title, Month(a.DocDate)
ORDER BY b.project_title, Month(a.DocDate);
|
The result will be like this:
| project_title | The_Month | Income |
| community radio | 1 | 1951.6 |
| community radio | 2 | 375 |
| community radio | 3 | 250 |
| ... | ... | ... |
| global TV advertizing | 1 | 6041.5 |
| global TV advertizing | 2 | 20400 |
| global TV advertizing | 3 | 500 |
| ... | ... | ... |
3. The same query can be converted to MS-Access Crosstab Query. project_title
will be positioned in rows, month - in column titles, and income as value
in the cell crossing:
TRANSFORM Sum(a.DocTotal) AS Income
SELECT b.project_title
FROM
(SELECT a.projectid, a.DocNo, a.CustomerID, a.DocTotal, a.DocDate
FROM Invoice AS a INNER JOIN InvLines AS b
ON a.DocKey = b.DocKey
WHERE Year(a.DocDate) = 1999) AS a
INNER JOIN AdvProjects AS b
ON a.projectid = b.projectid
GROUP BY b.project_title
PIVOT Month(a.DocDate);
|
The result will be like this:
| project_title | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| community radio | 1951.6 | 375 | 250 | 3500 | 6900 | 18374 | 6175 | 200 | 467.5 | 1250 | 375 | |
| global TV advertizing | 6041.5 | 20400 | 500 | | | 400 | | | | 19082.5 | | 12500 |
4. The real business procedure of "Colombo Ltd" can be even more complex. In
addition to "Invoce" table there is "RInvoice" [Refunded Invoices] table. It
stores information about Orders that was canceled and money returned to customers.
It referes Invoce table by field RefundDocNo. Understanding that Refunded
Invoices can change our Company Balance Sheet, we need to involve RInvoice
table in Sales Reports. We can do it using UNION clause:
SELECT c.DocTyp AS Doc_Type, SUM(c.MoneyTotal) AS Income
FROM
(SELECT 'I' AS DocTyp, a.DocNo, a.CustomerID, a.DocTotal AS MoneyTotal,
a.DocDate
FROM Invoice AS a
WHERE Year(a.DocDate) = 1999
UNION
SELECT 'R' AS DocTyp, b.DocNo, b.CustomerID, (b.DocTotal * -1) AS MoneyTotal,
b.DocDate
FROM RInvoice AS b
WHERE Year(b.DocDate) = 1999) AS c
GROUP BY c.DocTyp
|
The result will be like this:
| Doc_Type | Income |
| I | 54382.6 |
| R | -11575 |
|