Feeds:
Posts
Comments

Archive for January, 2014

I have had to use OLAP cube MEDIAN function to calculate a median value in the SSRS report. I can’t believe how you can now use this new function in SQL Server 2012 to achieve the same! 🙂 This new function is not available in versions before SQL Server 2012.

An example using AdventureWorks2012:

SELECT d.Name as DepartmentName, Rate
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)  OVER (PARTITION BY Name) AS MedianCont
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh
ON dh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory AS ph
ON ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL
AND d.Name in (‘Engineering’, ‘Document Control’, ‘Executive’, ‘Human Resources’)
ORDER BY  DepartmentName, Rate

The following shows Rate for each Department in an ascending order:

Percentile_Cont function to find a median value

The result of using the PERCENTILE_CONT to get a median Rate value for each department is shown below:

Median_Cont result

 

 

 

 

 

 

 

 

 

Advertisements

Read Full Post »

These new windowing functions are awesome. There are 8 of them i.e. LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, and PERCENTILE_DISC. I have tried lag, lead, first_value and last_value and could see the advantages of using them straight away.  🙂

http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx

An example using AdventureWorks sample database:

select * from HumanResources.EmployeePayHistory where BusinessEntityID = 174

select BusinessEntityID, RateChangeDate, Rate, Lag(Rate) OVER (partition by BusinessEntityID order by RateChangeDate) as PreviousRateChange
,LEAD(Rate) OVER (partition by BusinessEntityID order by RateChangeDate) as NextRateChange
from HumanResources.EmployeePayHistory
where BusinessEntityID = 174

Lag and Lead functions in SQL Server 2012

Read Full Post »

I am working on setting up an email notification to alert certain group of people when they have reached a target. The target is obtained from the Execute SQL Task and it is in XML format, which gets stored into a variable of type String in SSIS. The Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string. The content of the email looks like below.

<ROOT>A has a Perfect Year, B has a Perfect Quarter, C has a Perfect Quarter, D has a Perfect Month, E has a Perfect Month</ROOT>

To remove these tags, create a second variable of type String and another Execute SQL Task. Use REPLACE SQL function to remove the tags.

SELECT LTRIM(REPLACE(REPLACE( ‘<ROOT>A has a Perfect Year, B has a Perfect Quarter, C has a Perfect Quarter, D has a Perfect Month, E has a Perfect Month</ROOT>’, ‘<ROOT>’, ”), ‘</ROOT>’, ”) )
AS Level2results

Its SSIS Expression is below:

” SELECT LTRIM(REPLACE(REPLACE( ” + “‘” + @[User::Level2XML] + “‘” + “, ” + “‘” + “<ROOT>” + “‘” + “, ”), ‘</ROOT>’, ”) )
AS Level2results “

Read Full Post »

%d bloggers like this: