The information is being extracted from Microsofts Northwind database. The detail is folded into
one year (the last year) for display purposes.
The sample program demonstrates how to creats a clean user interface to present
a summary of the Orders placed over the proceeding year. The first page shows a list
all the Inventory items and the quantities of each item sold. The data can be sorted on
every column, in ascending or descending order.
IFRAMES and DIV layers are used so the whole page does not have to be refreshed each time
the data is updated. Every effort was made to so that the updates were quick, with limited
return trips to the server and page rebuild were kept to a minimum.
On the ORDER-ITEMS page the user can drill-down on each Product to view the Dates, Customers
and Invoice numbers of the selected product sold. In A complete Order section there would be
a full Order Entry section.
ORDER TOTALS page gives a complete summary of each product and the number of sales for each month of
the years. There is also a complete year sales total in months.
ORDER CHART page displays a graph of years sales broken down by Month and graphicly showing the
most active months.
Microsoft SQL Server 2005 with Stored Procedures.
SELECT o1.OrderID as Invoice, c1.CompanyName as Company,
left(convert(varchar(11), o1.OrderDate, 113),7) + ltrim(str(year(getdate())-1)) as 'Invoice date'
FROM Orders as o1
LEFT OUTER JOIN Customers as c1 on c1.CustomerID = o1.CustomerID
LEFT OUTER JOIN [order details] as o2 on o1.OrderID = o2.OrderID
WHERE o2.ProductID = @ProductID
ORDER BY o1.OrderID
Thanks you to the many people whose insights and sharing have contributed to the design and
devlopment of this section. Where possible the authors credits have been left with the coding.
Sometimes it has been difficult to establish who is the real owner as the same pieces of code keep
appearing in many publications. Currently a complex web based POS and Accounting package is
being designed in ASP.Net. Portions of its coding will appear here as time permits.