SQL Server – New Analytical functions in SQL Server 2012 – Lead and Lag
LAG:
Using this function we can fetch the nth row before (Lag) the Current row without using self-join.
It is useful when we need to compare the current row values with values in a previous row.
Syntax:
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
EXAMPLE:
Create an Employee table and insert some records to it.
CREATE TABLE Employee (EmpID int, HikeDate date, Salary money)
GO
INSERT INTO employee values (1, '2009-04-01 00:00:00.000',10000),
(1, '2010-04-01 00:00:00.000',17000),
(1, '2011-04-01 00:00:00.000',23000)
GO
SELECT EmpID, HikeDate, Salary
FROM Employee
ORDER BY HikeDate
Output:
Now my requirement is to show what the previous Salary before current hike was?
Solution Using Lag function:
LEAD:
Using this function we can fetch the nth row after (LEAD) the current row without using self-join.
It is useful when we need to compare the current row values with values in a following row.
Syntax:
LEAD (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
Example:
My requirement is to show what my following year salary is?
Solution using Lead function:
Note: Default offset value is 1. We can specify offset value by a column or a sub query, or other expression that evaluates to a positive integer. Negative value or analytical function can’t be used as offset value.
If you liked this post, do like on Facebook at https://www.facebook.com/s4sql
SQL Server – Error handling using Throw
MS-SQL Server 2012 introduced a new way of error handling using THROW statement. Prior to SQL Server 2012 we had to use RAISEERROR for error handling.
Throw statement raises an exception and transfers the execution to a CATCH block of a TRY…CATCH construct. Session will be ended if Throw is used without TRY…CATCH construct.
Syntax:
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }
] [ ; ]
To use error number in throw statement, it is not required to add the message in sys.messages table but the error number should be greater than equal to 50000. It can only raise severity of 16. Throw can be used without parameter in side CATCH construct.
You can find the difference between THROW and RAISERROR here.
The interesting point about throw is that you can re-throw the original error information from the catch block without passing any parameter.
Example:
Begin Try
print 'Start'
Begin Try
Declare @Name varchar(20) = '$XYZ'
If (LEFT(@Name,1)='$')
Begin
Throw 51234,'Name should not starts with $',1
End
End Try
Begin Catch
Throw;
End Catch
End Try
Begin Catch
Select ERROR_MESSAGE() As Error
End Catch
Output:
Reference: http://msdn.microsoft.com/en-us/library/ee677615
If you liked this post, do like on Facebook at https://www.facebook.com/s4sql
Sql Server 2012 function EOMONTH()
It returns the last day of the month for the given date with an optional offset.
Syntax : EOMONTH ( start_date [, month_to_add ] )
It returns date type.
Example 1:
DECLARE @date DATETIME
SET @date = ’12/1/2011′
SELECT EOMONTH ( @date) AS LastDateOfTheMonth
GO
Output : 
Example 2: Using offset
DECLARE @date DATETIME
SET @date = ’12/1/2011′
SELECT EOMONTH ( @date, 1) AS LastDateOfTheMonth
GO
Output: 
About
Sandip Pani is a MSBI/SQL Developer. He holds a Masters of Computer Application degree from IGIT, Sarang, Odisha, India and a number of certifications such as MCTS, MCITP. Befor Joining QSIHealthcare he was working for IBM India Pvt. Ltd and DELL Services. He has passion to contribute to MS-SQL Server community so he is committed to SQL.
You can contact me at s4sql@sqlcommitted.com
SQLcommitted-
Archives
- March 2013 (1)
- January 2013 (3)
- December 2012 (2)
- November 2012 (1)
- August 2012 (4)
- July 2012 (2)
- June 2012 (2)
- May 2012 (1)
- April 2012 (5)
- March 2012 (1)
- February 2012 (1)
- January 2012 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
