Nth Highest Salary in Employee Table

-- Query to find the Nth Highest Salary in Employee Table

SELECT EMPNO, ENAME, SAL

FROM

(

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS N,

EMPNO, ENAME, SAL FROM EMP

) DT

WHERE N = 3

I used N=3, as I need the third highest salary. We can check the data for various ranks.

Also check how the below window functions return data differently.

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

EOMONTH - New Function in SQL Server 2012

EOMONTH: Returns the last day of the month for the specified date. Also picks up previous or next month's last day of month using an optional argument. So no more expressions with nested date functions :)

Syntax: EOMONTH(<DateColumn>, <month_to_add>)

Previously, we used to run expressions like below for Last Day Of Previous Month, Last Day of Current Month and Last Day of Next Month.

Code:

SELECT

[Last Day of Previous Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(GETDATE())),GETDATE()), 101),

[Last Day of Current Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())), 101),

[Last Day of Next Month] = CONVERT(VARCHAR(10), DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))), DATEADD(MONTH, 2, GETDATE())), 101)

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

New Code: Using EOMONTH function, the expressions are simple now.

SELECT

EOMONTH(GETDATE(), -1) AS [Last Day Of Previous Month],

EOMONTH(GETDATE()) AS [Last Day Of Current Month],

EOMONTH(GETDATE(), 1) AS [Last Day Of Next Month]

Conclusion: Thanks to Microsoft SQL Server 2012 for making such frequently used expressions into a simple functions. Hope this helps! Happy TSQL'ing.

Multiple Result Sets in single Stored Procedure

SQL Server 2012 introduces a new feature called "WITH RESULT SETS".
It makes the stored procedure returning multiple result sets. Lets see an illustration on how this new thing works.

Stored Procedure Code:
CREATE PROCEDURE dbo.usp_MultipleResultSets
AS
BEGIN
SET NOCOUNT ON;

-- Department Result Set
SELECT 
[DepartmentID]
,[Name]
,[GroupName] 
FROM [HumanResources].[Department]

-- Employee Result Set
SELECT 
[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
FROM [HumanResources].[Employee]

END

Result Set:

EXEC dbo.usp_MultipleResultSets WITH RESULT SETS

(

(

DepartmentID smallint,

Name dbo.Name,

GroupName dbo.Name

),

(

[BusinessEntityID] [int],

[NationalIDNumber] [nvarchar](15),

[LoginID] [nvarchar](256),

[OrganizationNode] [hierarchyid],

[JobTitle] [nvarchar](50),

[BirthDate] [date],

[MaritalStatus] [nchar](1),

[Gender] [nchar](1),

[HireDate] [date],

[SalariedFlag] [dbo].[Flag],

[VacationHours] [smallint],

[SickLeaveHours] [smallint],

[CurrentFlag] [dbo].[Flag]

)

)

Hope it helps! Happy TSQLing!