Tag Archives: Analytical functions in SQL Server 2012

Using DATEFROMPARTS() – SQL Server 2012

SQL Server 2012 March 7, 2014 Leave a comment

DATEFROMPARTS() returns a date value for the specified year, month, and day.

Let us understand this with an example.

The SQL code for returning day, month and year parameters as a date would probably look something like this

DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' +
CONVERT(varchar(10),@Month) + '-' +
CONVERT(varchar(10),@Day),103) AS TheDate

datetime1Now lets implement DATEFROMPARTS

Here is the code for the same

DECLARE @Day INT = 07,
@Month int = 03,
@Year INT = 2014
SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate

datetime2

Advertisement

How to troubleshoot error – Msg 195, Level 15, State 10, Line 1 ‘TRY_CONVERT’ is not a recognized built-in function name

SQL Server 2012, Troubleshooting February 19, 2014 Leave a comment

While running the following code on a database on my SQL Server 2012 instance I got the following error message

USE BB
go
SELECT TRY_CONVERT(xml, 'MyClub')

TCerr1The reason for this error is that this function TRY_CONVERT is 110 compatible. Which means it will only run on a database with 110 compatibility. Upon checking the compatibility level, I found the database to be on 90 or SQL Server 2005 compatible.
TCerr2Resolution:
I had to change the compatibility level to 110 and then ran the code with success.

TCerr3

TRY_CONVERT function in SQL Server 2012

SQL Server 2012 February 18, 2014 Leave a comment

TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows
TryCon1

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
TryCon3The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

TryCon4CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TryCon5TRY_CONVERT will give a NULL.

 

CHOOSE – SQL Server 2012

SQL Server 2012 February 17, 2014 Leave a comment

In this article we will understand this new logical function named CHOOSE in SQL Server 2012.

So here is how the syntax of CHOOSE looks like

 CHOOSE ( index, val_1, val_2 [, val_n ] )

Now let us use this in the below code and understand the output.

declare @MyFavouriteClub as int = 5
SELECT choose(@MyFavouriteClub, 'Arsenal', 'Chelsea',
'Liverpool', 'Everton', 'Manchester United',
'Sunderland', 'Fulham')

Now lets see the output of the above
choose1So what the function does is pulls out the 5th value from the data set and dispalys as a result set. CHOOSE works like an index into an array, where the array contains values or arguments. The index value\argument determines which of the containing values will be returned. If no match is found then a NULL is returned.

PERCENTILE_DISC function in SQL Server 2012

SQL Server 2012 February 16, 2014 Leave a comment

PERCENTILE_DISC is one the new analytical\statistical functions that we have in SQL Server 2012. In this article we will see how we can use this function. To begin with let us see the syntax of this function as stated in BOL.

Syntax of PERCENT_DISC

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP
( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )

Now let us implement PERCENT_DISC and understand how it works.We will be using the below script to create a database and insert some values in it before we call this function.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Noe let’s use the below code to implement this function. With this code we are trying to find the 50th percentile from table we created.

USE PERCENTRANK
GO
select ProductId, SalesQty, Year,
PERCENTILE_DISC(.5)
WITHIN GROUP(ORDER BY SalesQty)
OVER(PARTITION BY ProductID) as [50Percentile]
from PERCENTRANKDEMO
WHERE ProductId = 20
order by ProductId;

Snapshot of the output of the above code
Perc1So what is the inference that we draw from the output. The function PERCENTILE_DISC calculates the Nth percentile based on a discrete distribution of the column values. The result set would have the Nth percentile value which would equal to a specific value in the column. Any NULLS would be ignored.

PERCENT_RANK function in SQL Server 2012

SQL Server 2012 February 13, 2014 Leave a comment

PERCENT_RANK is a new function introduced in SQL Server 2012. The function calculates the relative rank of a row within a subset of rows or in simpler terms  it shows the percentage of values that are less than or equal to the current value.

The syntax for the function is as follows

 PERCENT_RANK( )
 OVER ( [ partition_by_clause ] order_by_clause )

Let us quickly implement PERCENT_RANK and understand how it works.

Use the following code to create a database with values in it.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.

SELECT ProductId,SalesQty,Year,
PERCENT_RANK() OVER ( ORDER BY [SalesQty] )
AS PercentRank
FROM PERCENTRANKDEMO
WHERE productid = 30;

The outcome would be as below. The column PercentRank shows the percent of values that are less or equal to the current value of the SalesQty column. The first row in any set has a PERCENT_RANK of 0. That’s how it has been designed by default. In the result set 0.1 denotes 10 percent while 1 denotes 100 percent.
PR1

LAG and LEAD functions in SQL Server 2012

SQL Server 2012 February 12, 2014 1 Comment

LAG() and LEAD() are two of the new analytical functions that has been introduced in SQL Server 2012. These functions helps in accessing a value from a  previous row (lag) and subsequent row (lead) in the same result set without joining the result set to itself . Let us quickly understand this with an example.

Let us create a database for the same and insert some values. Use the following code to achieve the same on a SQL Server 2012 instance

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'LagAndLeadDemo')
BEGIN
 DROP DATABASE LagAndLeadDemo;
END
GO
CREATE DATABASE LagAndLeadDemo;
GO
USE LagAndLeadDemo
GO
CREATE TABLE LagLead
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into LagLead
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,3093,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Now let us implement LAG with following query

select [ProductId], [SalesQty], Year,
LAG([SalesQty]) OVER
(ORDER BY Year) as SalesQtyLastYear
from LagLead
where [ProductId] = 10
order by Year;

ll2Now if we observe the data of the column ‘SalesQtylastYear’ then we will find that it reflects the value that belongs to the previous row of the SalesQty column.

Now let us implement Lead with the following query

select [ProductId], [SalesQty],
Year,
LAG([SalesQty]) OVER
(ORDER BY Year) as SalesQtyLastYear,
Lead([SalesQty]) OVER
(ORDER BY Year) as SalesQtyNextYear
from LagLead
where [ProductId] = 10
order by Year;

ll3What we observe now is that the data of the column ‘SalesQtylastYear’ reflects the next value that belongs to the SalesQty column.

So in a nutshell this is what LAG and LEAD does. Fetches the previous row and next row values respectively. Let us now calculate the difference between last years sales quantity and current year’s sales quantity

select [ProductId], [SalesQty], Year,
LAG([SalesQty]) OVER (ORDER BY Year)
as LastYearRevenue,
[SalesQty] - LAG([SalesQty]) OVER (ORDER BY Year)
as SalesQtyChange
from LagLead
where [ProductId] = 20
order by Year;

ll4

FIRST_VALUE – New analytical function in SQL Server 2012

SQL Server 2012 January 22, 2014 Leave a comment

In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

%d bloggers like this: