In the first part of this series, we saw the structure of SELECT Statement, how to join multiple tables, operators and building a subquery.
Today we will see Nested Table expression that is very frequently used while writing Stored Procedures or Views. By using this expression we are creating a temporary view from a query that is defined in the FROM Clause. This helps when you are trying to apply some aggregations on a set of data and use the result set as a temporary table. This temporary table takes the spot of an actual table name in the FROM clause and acts like a virtual table.
We’ll use the Human Resource schema for all the examples in this post. See how department information is stored in Department table and the history for each employee’s department is stored in “EmployeeDepartmentHistory” Table. Note the usage of Start and End date that basically tells which department id an employee belonged to at any point in history. Details of employees are as you can see stored in the “Employee” table.
Before we construct a nested query, let us take a peek at the data for each of the tables we’ll be using in our final query.
**This is partial result set for Employee Table.
Partial data for EmployeeDepartmentHistory table.
Data from Department table
In order to construct a nested query, let’s make up a scenario where we want to find the Total of Average vacation hours by salaried and non salaried employees across the company.
We’ll also add some rules to our query.
- Results should consist of record for only those employees who took more than 20 Sick Leave hours in the selected time frame.
- Result should include employees from Sales, Purchasing, Marketing or Engineering department only.
Here is the final query which we will dissect and understand after the jump.
SELECT SUM(AVG_VacationHours) AS TotalOfAvgVacHours, AVG(TotalTimeOff) AS AVGTotalTimeOff, SalariedFlag
AVG(VacationHours) AS AVG_VacationHours
,SUM(SickLeaveHours+VacationHours) AS TotalTimeOff
,EmpHistory.DepartmentID AS DeptID
FROM AdventureWorks2012.HumanResources.Employee AS Employee INNER JOIN AdventureWorks2012.HumanResources.EmployeeDepartmentHistory AS EmpHistory
ON Employee.BusinessEntityID = EmpHistory.BusinessEntityID
INNER JOIN AdventureWorks2012.HumanResources.Department AS Department
ON Department.DepartmentID = EmpHistory.DepartmentID
WHERE HireDate between '01-05-2005' AND GETDATE()
AND SickLeaveHours >20
AND Department.Name IN ('Sales','Purchasing','Marketing','Engineering')
GROUP BY OrganizationLevel, SalariedFlag, EmpHistory.DepartmentID, Name
We start by selecting the items we want for forming our virtual table, the result of which will be used in the From Clause of the main query. The SQL in the nested expression is used in place of an actual table and meets our rules that I mentioned earlier.
In the nested query, we’ll do an Average of vacation hours and Sum up Sick Leave days with Vacation days for employees hired between 01-05-2005 to date. We’ll also pick the departments in this query and only those employees who took more than 20 hours sick leave.
Here is the result set for the nested query.
Now this is the data of a virtual table that will be used in the FROM clause of main query. In the main query, we will sum up all the averages for vacation hours and group it by Salaried Flag.
The end result tells us how many average vacation hours salaried employees and unsalaried employees(contractors) took. The query also tells us the average time off for both categories of salaried and non salaried employees.
Here is the result set again.