SQL advanced -> Sales Reports using MS-Access SQL

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:
DocNoDocKeyDocDateDocTotalCustomerNameSlpCode...
110014/01/992375Angelina Alba11...
210122/01/991291.5Pestiana Oliviera11...
310214/02/9910200Enrice Durance10...
410310/03/99250Jessica Simpson8...

InvLines:
DocKeyItemCodeItemNameQnt...
1000036876Memorex 8.5Gb20...
10077003Panasonic DMR-E3...
1015003258Philips DVP64245...


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_MonthIncome
17993.1
220775
3750
......


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_titleThe_MonthIncome
community radio11951.6
community radio2375
community radio3250
.........
global TV advertizing16041.5
global TV advertizing220400
global TV advertizing3500
.........


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_title123456789101112
community radio1951.637525035006900183746175200467.51250375  
global TV advertizing6041.520400500     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_TypeIncome
I54382.6
R-11575



sqlexamples.info