Monthly Archives: February 2014

R.A.I.D Document

In my earlier post “8 Must-Have Documents in a Project Manager’s Briefcase” I mentioned R.A.I.D document. Today I will dive a little deeper and show you some examples. R.A.I.D stands for Risk, Assumptions, Issues and Decisions. You might see different variations of this document; some people like to use “Dependencies” instead of “Decisions” whereas others use “Action” in place of “Assumption”. Personally I feel that the dependencies are anyways captured as a part of issues and action items are also listed for mitigation of  risk and issues log. So it would make more sense to use the categories – Risk, Assumptions, Issues and Decisions.

This is what I wrote in my earlier post and I am just pasting it back instead of reinventing the wheel!

R.A.I.D Document- This document will have sections for documenting and maintaining risks, assumptions, issues and decisions logged throughout the project lifecycle.

  • Risk Log – This document would contain the detail about the risk, affected components, impact on timeline, level of severity, team member(s) responsible for resolving the risk, action needed, cause of the risk, impacted group, impacted work packages, resolution target date etc. All risks should be numbered uniquely and in the order of discovery and should be referred by that number throughout the project life cycle.

RAID Document - Analyticalviews.com

  • Assumptions- This section will list out all the assumptions before the commencement of project and during project execution as you follow through your activities. Every project will have assumption such as availability of required data, estimated disk space for database, availability of required software and things of that nature.

RAID Document - Analyticalviews.com

  • Issue log – This section is similar to risk log and would contain list of all the issues numbered uniquely and in order they were received along with the impact of the issue, priority,  affected components, team member(s) responsible for resolving the issue, status indicator, actual reported date, projected completion date, dependencies etc. Entries in the issue log should never be deleted or moved once they are resolved or there is change in status or priority and should have unique id so they can be refereed throughout the project by that id.

RAID Document - Analyticalviews.com

  • Decisions- This section documents the decisions taken by any individual authorized in their position to do so, decision details, department/group, date recorded, impacted and affected components.

RAID Document - Analyticalviews.com

Hope this is helpful to anyone trying to build a template. Or just contact me if you need my excel sheet.

SQL Tutorial – Data Retrieval (Part 2)

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.

Human Resource Schema

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.

SELECT
LoginID
 ,VacationHours
,SickLeaveHours
,OrganizationLevel, SalariedFlag
FROM AdventureWorks2012.HumanResources.Employee

Employee Table

Partial data for EmployeeDepartmentHistory table.

Resultset for EmpDeptHistory

Data from Department table

Department table resultset

 

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
FROM (
SELECT
AVG(VacationHours) AS AVG_VacationHours
,SUM(SickLeaveHours+VacationHours) AS TotalTimeOff
,OrganizationLevel
,EmpHistory.DepartmentID AS DeptID
,Department.Name
,SalariedFlag
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
)
AS
Temp_Table
GROUP BY Temp_Table.SalariedFlag

Result:
Final Result for Nested Query

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.

Nested Query Result set

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.

Final Result for Nested Query