INTRODUCING ADVENTURE WORKS CYCLES DATABASE in VS .NET

Printer Data Matrix ECC200 in VS .NET INTRODUCING ADVENTURE WORKS CYCLES DATABASE

When we run this statement in SQL Server, we can see that the Adventure Works business currently employs 290 staff as shown in figure 1.9. Next we ll expand upon that query by grouping the staff hires by the year in which they began work. By doing this we can see how the business has grown. To create the report we still only need to query the employee table to get the data we need. Enter the SQL code and run the statement.

Figure 1.9 Employee count results SELECT [Year Of Hire] = YEAR(HireDate), [Employees Hired] = COUNT(EmployeeID) FROM HumanResources.Employee GROUP BY YEAR(HireDate) ORDER BY YEAR(HireDate) DESC

Figure 1.10 shows us the result of running this statement; and we can see from it that 1999 stands out as the company s biggest year for employee hires, with 198 employees hired. In listing 1.3 we ve altered our previous query slightly so that we can see how the 290 employees that are currently employed are distributed within the departments of the business. To present this data in a meaningful way requires us to join the EmployeeDepartmentHistory and Department tables.

SELECT DepartmentName = dept.[Name], EmployeeCount = COUNT(e.EmployeeID) FROM HumanResources.Employee e, HumanResources.EmployeeDepartmentHistory hist, HumanResources.Department dept WHERE hist.EmployeeID = e.EmployeeID AND hist.DepartmentID = dept.DepartmentID AND hist.EndDate IS NULL GROUP BY dept.[Name] ORDER BY EmployeeCount DESC

We can see that the where clause for this query specifies that the EndDate for the EmployeeDepartmentHistory entry must be null. This ensures that we are only including the current record for a given employee, and won t double-up for an employee who has multiple entries such as an employee who was transferred

between departments. Figure 1.11 shows us that, when we run the query, we can see that the production department has by far the most employees at 179. Finally, we can write a query that allows us to see the number of sales that are made by Adventure Works Cycles to support its 290 employees. To obtain this information, we simply need to query the SalesOrderHeader table and add up the value of sales orders for each year. The following snippet provides the code to do this:

SELECT FiscalYear = YEAR(DATEADD(m, 6, soh.OrderDate)), SalesAmount = SUM(soh.SubTotal) FROM Sales.SalesOrderHeader soh GROUP BY YEAR(DATEADD(m, 6, soh.OrderDate)) ORDER BY SalesAmount DESC

The results for this query are shown in figure 1.12. From the results we can see that sales have grown for each of the three years that the business has been operating. Looking at these queries helps us get a feel for the business. From these queries we can see that the sales have grown exceptionally between the years 2002 and 2005. In this time sales have more than doubled from $2.7 million to just over $6 million. It was therefore not surprising that, looking at the headcounts for each department, production and sales lead the way. In fact, of the 290 employees that the business currently employs, nearly 200 of them work in the production area. With more than five years worth of sales, HR, production, and purchasing data, the Adventure Works Cycles business provides us with an ample scope for building interesting applications, which is lucky because that s exactly what we are about to do! 1.4.3 Getting our hands on data In order to create a portal for the Adventure Works business, we will need data. Before we can use that data, our first task is to create the code that can access the SQL Server database and return it for us. This type of code is generally known as data access code and it sits within a conceptual application layer commonly referred to as the data layer. In this section we ll be creating the data access methods and the necessary logic to get data for the human resources department at Adventure Works. To do