New features in SQL Server 2012

LAG() Function to fetch Previous Value

Syntax: LAG (col [, offset], [default])  OVER ([partition by col] order by col)

Offset argument determines the number of previous rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LAG() function

The default argument sets the value which will be returned if SQL Lag() function returns nothing.

CREATE TABLE #EMP_SALARY (ID INT,EMP_ID INT,SAL_MONTH VARCHAR(10),SALARY INT)

INSERT INTO #EMP_SALARY VALUES (1,1,'Jan-13',5000),(2,2,'Jan-13',6000),(3,1,'Feb-13',6000),(4,2,'Feb-13',4000)

SELECT * FROM #EMP_SALARY ORDER BY EMP_ID,ID

ID EMP_ID SAL_MONTH  SALARY

-- ------ ---------- ------

1   1      Jan-13     5000

3   1      Feb-13     6000

2   2      Jan-13     6000

4   2      Feb-13     4000

SELECT ID,EMP_ID,SAL_MONTH,SALARY,LAG(SALARY,1,NULL) OVER(PARTITION BY EMP_ID ORDER BY ID) AS PREV_SAL

FROM #EMP_SALARY

--Output:

ID EMP_ID SAL_MONTH  SALARY PREV_SAL

-- ------ ---------- ------ --------

1   1      Jan-13     5000  NULL

3   1      Feb-13     6000  5000

2   2      Jan-13     6000  NULL

4   2      Feb-13     4000  6000

<< Exception handling                                  Go to Main Page                                           EOMONTH()>>