Table of Contents


EOMONTH
We had a problem whenever we wanted to identify the end date of a month. There was no built in function. But now that problem is solved in SQL Server 2012. The function EOMONTH returns the date of the month.

SELECT   EOMONTH('05/02/2012') AS 'EOM Processing Date'

Output: 2012-02-29

You can specify a number of months in the past or future with the EOMONTH function.

SELECT   EOMONTH ( Getdate(), -1 ) AS  'Last Month'

Output: 2012-01-31


CHOOSE
Using this to select a specific item from a list of values.

SELECT   CHOOSE ( 4, 'CTO', 'GM', 'DGM', 'AGM', ’Manager’ )

Output: AGM


CONCAT
This function is concatenating two or more strings

SELECT CONCAT( emp_name,'Joining Date', joingdate)
Output: Rahman Joining Date 01/12/2001

LAST_VALUE and FIRST_VALUE
Using the function you can last value among the set of ordered values according to specified ordered & partitioned criteria. First value return the first value in an ordered set of values.

INSERT INTO result(Department ,ID ,Marks ) VALUES (1,103,70), (1,104,58) (2,203,65) (2,201,85)

SELECT  Department,Id ,Marks, LAST_VALUE(Marks) over (Partition by Department ORDER BY Marks) AS
'Marks Sequence' ,FIRST_VALUE (Marks) over (Partition by Department ORDER BY Marks) as First value’
FROM  result
OutPut
Department   Id        Marks      Marks Sequence      First value

1                 104       58            58                           58
1                 103       70            70                           58
2                 203       65            65                           65
2                 201       85            85                           65
LEAD
Using the function you can accesses data from a subsequent row in the same result set without the use of a self-join.
SELECT EntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS  CurrentQuota,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS  PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275  and   YEAR(QuotaDate) IN ('2005','2006');

OutPut
EntityID      SalesYear   CurrentQuota     PreviousQuota
---------------- -----------    ---------------------    ---------------------
275              2005        367000.00             556000.00
275              2005        556000.00             502000.00
275              2006        502000.00             550000.00
275              2006        550000.00             1429000.00
275              2006        1429000.00            1324000.00
275              2006        1324000.00            0.00

File Group Enhancement:
A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files.


See Also


Other Languages