Ranking Functions & Aggregate Functions & Analytic Functions

• Ranking window functions

1. Row_Number()
2. Rank()
3. Dense_Rank()
4. Ntile()

• Aggregate window functions
  • MIN, MAX, AVG, SUM
  • COUNT, COUNT_BIG
  • CHECKSUM_AGG
  • STDEV, STDEVP, VAR, VARP
• Analytic window functions
  • LAG, LEAD
  • FIRST_VALUE, LAST_VALUE
  • PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST

Ranking Functions

use AdventureWorksDW

go

SELECT *,

  ROW_NUMBER() OVER(ORDER BY salesreasonreasontype DESC) AS RowNumber,

  RANK() OVER(ORDER BY salesreasonreasontype DESC) AS BasicRank,

  DENSE_RANK() OVER(ORDER BY salesreasonreasontype DESC) AS DenseRank,

  NTILE(3) OVER(ORDER BY salesreasonreasontype DESC) AS NTileRank

FROM

  DimSalesReason

 results returned by the SELECT statement.

Aggregate Functions

use AdventureWorksDW

go

SELECT

  title,departmentname,

  COUNT(baserate) OVER(PARTITION BY departmentname) AS CountryCount,

  SUM(baserate) OVER(PARTITION BY departmentname) AS TotalSales,

  AVG(baserate) OVER(PARTITION BY departmentname) AS AverageSales

FROM

  DimEmployee

ORDER BY

  title,departmentname DESC;

RESULT:

Analytic Functions

SELECT

  SalesGroup,

  Country,

  AnnualSales,

  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup

    ORDER BY AnnualSales DESC) AS HighestSales,

  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup

    ORDER BY AnnualSales DESC) AS LowestSales

FROM

  RegionalSales;

OUTPUT:

SalesGroup	Country	AnnualSales	HighestSales	LowestSales
Europe	United Kingdom	32000	32000	32000
Europe	Germany	22000	32000	22000
Europe	France	19000	32000	19000
Europe	Italy	18000	32000	18000
Europe	Greece	16000	32000	16000
Europe	Spain	16000	32000	16000
North America	Canada	32000	32000	32000
North America	Mexico	28000	32000	28000
North America	United States	22000	32000	22000
Pacific	China	28000	28000	28000
Pacific	Japan	22000	28000	22000
Pacific	Singapore	21000	28000	21000
Pacific	Malaysia	19000	28000	19000
Pacific	Australia	18000	28000	18000
Pacific	New Zealand	18000	28000	18000
Pacific	Thailand	17000	28000	17000

DateTime Functions

• DateTime Function in SQL Server

• GETDATE()

• DATEPART()

• DATEDIFF()

• DATENAME()

• DAY()

• MONTH()

• YEAR()

GETDATE():

 This value is derived from the operating system of the computer on which the instance of SQL Server is running.

SELECT GETDATE() as TODAY

Ans:

DATEPART():

It returns the part of the date like YEAR,MONTH,WEEK,DAY,QUARTER,DAYOFYEAR,WEEKDAY,HOUR,MINUTE
,SECOND and MILLISECOND



 DATEDIFF():

Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

ex:

SELECT DATEDIFF(YEAR,HIREDATE,GETDATE())AS DIFFERENCE FROM EMP

DATENAME():

SELECT DATENAME(DAY, '2007-06-01')as dayinmonth,DATENAME(DY, '2007-06-01')as dayinyear,DATENAME(WEEKDAY, '2007-06-01')as weekday;

ans:

DAY():

Returns an integer representing the day (day of the month) of the specified date.

Ex:

SELECT DAY(GETDATE())AS DAYOFMONTH

MONTH():

SELECT MONTH(GETDATE())AS MONTHOFYEAR

YEAR():

SELECT YEAR(GETDATE())AS YEAR

CONVERSIONS

note:conversions are collected from different websites
declare @date datetime

set @date='2012-03-16 '

SELECT convert(varchar, @date, 103)
 

for further reference below you can find all the possible format for a date

100 – mon dd yyyy hh:mmAM (or PM)
    – Oct  2 2008 11:01AM         
101 – mm/dd/yyyy - 10/02/2008                 
102 – yyyy.mm.dd – 2008.10.02          
103 – dd/mm/yyyy
104 – dd.mm.yyyy
105 – dd-mm-yyyy
106 – dd mon yyyy
107 – mon dd, yyyy
108 – hh:mm:ss
109 – mon dd yyyy hh:mm:ss:mmmAM (or PM)
    – Oct  2 2008 11:02:44:013AM  
110 – mm-dd-yyyy
111 – yyyy/mm/dd
112 – yyyymmdd
113 – dd mon yyyy hh:mm:ss:mmm
    – 02 Oct 2008 11:02:07:577    
114 – hh:mm:ss:mmm(24h)
120 – yyyy-mm-dd hh:mm:ss(24h)
121 – yyyy-mm-dd hh:mm:ss.mmm
126 – yyyy-mm-ddThh:mm:ss.mmm
     – 2008-10-02T10:52:47.513

 Remaining date functions in sql2012

SELECT Datefromparts(2010, 5, 14) AS [DateParts]

SELECT Dateadd(mm, 2, Getdate()) AS [DateAdd]

SELECT Datediff(mm, '2014-01-01', Getdate()) AS [DateDiff]

SELECT Datename(day, Getdate()) AS [dayname]

SELECT Datepart(mm, Getdate()) AS [DATEPART]

SELECT Datetime2fromparts(2014, 12, 30, 4, 5, 6, 7, 1) AS [DATETIME2FROMPARTS]

SELECT Datetimefromparts(2010, 10, 25, 10, 11, 22, 9) AS[DATETIMEFROMPARTS]

SELECT Switchoffset(Sysdatetimeoffset(), '+06:30') AS [SWITCHOFFSET]

SELECT Datetimeoffsetfromparts(2014, 08, 25, 10, 10, 20, 1, 5, 30, 6)

AS [DATETIMEOFFSETFROMPARTS]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Getdate() AS [GETDATE]

SELECT Getutcdate() AS [GETUTCDATE]

SELECT Isdate('2014-06-20') AS [ISDATE]

SELECT Eventdata() AS [EVENTDATA]

SELECT Objidupdate(1) AS [OBJIDUPDATE]

SELECT Datalength('2014-05-25') AS [DATALENGTH]

SELECT Stats_date(2, 12563) AS [STATS_DATE]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Sysutcdatetime() AS [SYSUTCDATETIME]

SELECT Todatetimeoffset('2014-06-03', 500) AS [TODATETIMEOFFSET]