Choose in SQL Server 2012

Overview

SQL Server 2012 has introduced 2 new logical functions. IIF & Choose.
Let's go over CHOOSE function. It returns a value at specific index from a list. 

Scenarios & Example

•  Basic Use

SELECT CHOOSE(3, 'SQL 2005', 'SQL 2008', 'SQL 2012')

(No column name)

SQL 2012

So we can see that, it gives 3rd value from the set of value we have provided. 
Let's test if we give some unusual index values

SELECT 'CASE 1.1' [Case], CHOOSE(1.1, 'SQL 2005', 'SQL 2008', 'SQL 2012')  [Value]

UNION ALL

SELECT 'CASE 1.9' , CHOOSE(1.9, 'SQL 2005', 'SQL 2008', 'SQL 2012')

UNION ALL

SELECT 'CASE -3' , CHOOSE(-3, 'SQL 2005', 'SQL 2008', 'SQL 2012')

UNION ALL

SELECT 'CASE 5' , CHOOSE(5, 'SQL 2005', 'SQL 2008', 'SQL 2012')

Case	Value
CASE 1.1	SQL 2005
CASE 1.9	SQL 2005
CASE -3	NULL
CASE 5	NULL

•  Advance Use

Let's say, we have a date stored in a table, we want to know whether it is weekday/weekend, then this is how we can write using CHOOSE.

SELECT EmployeeName,EmployeeDOB, DATENAME(DW,EmployeeDOB), DATEPART(DW,EmployeeDOB),

CHOOSE(DATEPART(DW,EmployeeDOB), 'WEEKEND', 'Weekday', 'Weekday', 'Weekday', 'Weekday', 'Weekday', 'WEEKEND') FROM Employee

EmployeeName	EmployeeDOB	(No column name)	(No column name)	(No column name)
John	4/4/1989	Tuesday	3	Weekday
Rob	5/28/1990	Monday	2	Weekday
Bob	3/16/1991	Saturday	7	WEEKEND
Ryan	12/5/1983	Monday	2	Weekday
Lisa	9/14/1979	Friday	6	Weekday

We can also write the same thing using CASE statement but the only thing CHOOSE will be easier to write than CASE.
w.r.t Performance, there is no difference either you use CASE or CHOOSE, both are same. CHOOSE internally uses CASE only.

First_Value and Last_Value

Overview

SQL Server 2012 introduces following analytical functions

• CUM_DIST & Percentile_DIST
• FIRST_Value & Last_Value
• LAG & LEAD
• Percent_Count & Percent_Range

First_Value & Last_Value functions allow you to get the same value for the first and last row for all records in a result set, it will also be useful in a scenario where you want to compare particular thing with max and minimum value of the same thing.

Syntax

FIRST_VALUE ( [scalar_expression ] ) 

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Scenarios & Example

• Find out highest and lowest value for particular resultset

Let's say we have a table called "Marks" which contain the marks of each student, each subject. and we want to compare marks of particular subject for each student with highest and lowest marks then this is how we can get it,

SELECT StudentName, Marks, FIRST_VALUE (Marks) OVER (ORDER BY Marks) LowestMarks

, LAST_VALUE (Marks) OVER (ORDER BY Marks) HighestMarksSoFar

, LAST_VALUE (Marks) OVER (ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks

FROM   Marks

WHERE  SubjectName = 'Maths'

StudentName	Marks	LowestMarks	HighestMarksSoFar	HighestMarks
Rob	67	67	67	100
Ryan	97	67	97	100
Bob	98	67	98	100
John	100	67	100	100

We can see that, when we use "Rows Between Unbound Preceding and Unbound Following" in LAST_Value function, then we are getting actual Highest value from that result-set otherwise it gives the highest value whatever we have got so far. This is NOT the case for First_Value function

• Find out highest and lowest value using Partition By clause

Let's say we want to get above result for all the subjects then in this case, we need to use Partition By clause.

SELECT SubjectName, StudentName, Marks, FIRST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks) LowestMarks

, LAST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks

FROM   Marks

order by 1,3

SubjectName	StudentName	Marks	LowestMarks	HighestMarks
English	Ryan	89	89	99
English	Bob	93	89	99
English	John	94	89	99
English	Rob	99	89	99
Maths	Rob	67	67	100
Maths	Ryan	97	67	100
Maths	Bob	98	67	100
Maths	John	100	67	100
Science	Rob	87	87	98
Science	Ryan	94	87	98
Science	John	98	87	98
Science	Bob	98	87	98

Lag and Lead

Overview

SQL Server 2012 introduces following analytical functions

• CUM_DIST & Percentile_DIST
• FIRST_Value & Last_Value
• LAG & LEAD
• Percent_Count & Percent_Rank

Out of these, LAG & LEAD is very useful in almost all applications, where we need to find previous and next value based on a specific result-set and we need to do the self-join, LAG & LEAD completely gets rid off this self join and makes it very easy.

Syntax

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Scenarios & Examples

• Compare values between years

Let's say we have TotalSales per year for each product and now I want to compare with previous year sales and next year sales. look at the below query and we can know how easy it is to derive using these functions, otherwise we need to do self-joins and it is always painful.

SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Order by SaleYear) PrevYearSales,

LEAD(TotalSales,1,0) OVER (Order by SaleYear) NextYearSales

FROM ProductSales

WHERE  ProductID = 2

ProductID	SaleYear	TotalSales	PrevYearSales	NextYearSales
2	2008	9000	0	96570
2	2009	96570	9000	67800
2	2010	67800	96570	78700
2	2011	78700	67800	90565
2	2012	90565	78700	0

0 is the default value, we will get 0 if function cannot derive anything. 

• Use LAG, LEAD along with GROUP By

Let's say, we want to find out aggregated value of TotalSales per year and we want to compare with previous and next year, then here is the query

SELECT SaleYear, SUM(TotalSales) TotalSales, LAG(SUM(TotalSales),1,0) OVER (Order by SaleYear) PrevYearSales,

LEAD(SUM(TotalSales),1,0) OVER (Order by SaleYear) NextYearSales

FROM ProductSales

group by SaleYear

SaleYear	TotalSales	PrevYearSales	NextYearSales
2008	28000	0	232070
2009	232070	28000	142600
2010	142600	232070	136500
2011	136500	142600	174465
2012	174465	136500	0

• Use LAG, LEAD within Partition

Let's say, we want to compare totalsales with previous and next year for all the products, then we can achieve this by integrating Partition by clause

SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Partition By ProductID Order by SaleYear) PrevYearSales,

LEAD(TotalSales,1,0) OVER (Partition By ProductID Order by SaleYear) NextYearSales

FROM ProductSales

ProductID	SaleYear	TotalSales	PrevYearSales	NextYearSales
1	2008	10000	0	10400
1	2009	10400	10000	47000
1	2010	47000	10400	14500
1	2011	14500	47000	78000
1	2012	78000	14500	0
2	2008	9000	0	96570
2	2009	96570	9000	67800
2	2010	67800	96570	78700
2	2011	78700	67800	90565
2	2012	90565	78700	0
3	2008	8000	0	67800
3	2009	67800	8000	4500
3	2010	4500	67800	9000
3	2011	9000	4500	4500
3	2012	4500	9000	0
4	2008	1000	0	57300
4	2009	57300	1000	23300
4	2010	23300	57300	34300
4	2011	34300	23300	1400
4	2012	1400	34300	0