|
|
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 (CASE Month(a.DocDate)
WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEB' WHEN 3 THEN 'MAR'
WHEN 4 THEN 'APR' WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN'
WHEN 7 THEN 'JUL' WHEN 8 THEN 'AUG' WHEN 9 THEN 'SEP'
WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC'
ELSE 'NNN' END) 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.
Monthes numbers converted to names using CASE function */
|
The result will be like this:
| The_Month | Income |
| JAN | 7993.1 |
| FEB | 20775 |
| MAR | 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 TSQL matrix using Group By + CASE
function:
SELECT c.project_title,
SUM(CASE c.Mon WHEN 1 THEN c.Income ELSE 0 END) AS Jan,
SUM(CASE c.Mon WHEN 2 THEN c.Income ELSE 0 END) AS Feb,
SUM(CASE c.Mon WHEN 3 THEN c.Income ELSE 0 END) AS Mar,
SUM(CASE c.Mon WHEN 4 THEN c.Income ELSE 0 END) AS Apr,
SUM(CASE c.Mon WHEN 5 THEN c.Income ELSE 0 END) AS May,
SUM(CASE c.Mon WHEN 6 THEN c.Income ELSE 0 END) AS Jun,
SUM(CASE c.Mon WHEN 7 THEN c.Income ELSE 0 END) AS Jul,
SUM(CASE c.Mon WHEN 8 THEN c.Income ELSE 0 END) AS Aug,
SUM(CASE c.Mon WHEN 9 THEN c.Income ELSE 0 END) AS Sep,
SUM(CASE c.Mon WHEN 10 THEN c.Income ELSE 0 END) AS Oct,
SUM(CASE c.Mon WHEN 11 THEN c.Income ELSE 0 END) AS Nov,
SUM(CASE c.Mon WHEN 12 THEN c.Income ELSE 0 END) AS Dec
FROM
(SELECT Month(a.DocDate) AS Mon,
b.project_title, SUM(a.DocTotal) AS Income
FROM
(SELECT a.projectid, 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 Month(a.DocDate), b.project_title) AS c
GROUP BY c.project_title
|
The result will be like this:
| project_title | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 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 |
|