Functions

use ReportBuilderTraining

--
-- CASE and type conversions
--

-- division by zero error
select 
	 TermCreditsEarned
	,TermCreditsAttempted
	,TermCreditsEarned / TermCreditsAttempted * 100 [Completion Rate]
	from StudentHistory

-- handle zero attempted credits, but we get 0 or 100 as results?
select 
	 TermCreditsEarned
	,TermCreditsAttempted
	,case 
		when TermCreditsAttempted > 0
		then TermCreditsEarned / TermCreditsAttempted * 100
	 end [Completion Rate]
	from StudentHistory

-- implicit conversion too ... ?
select 
	 TermCreditsEarned
	,TermCreditsAttempted
	,case when TermCreditsAttempted > 0
		then 1. * TermCreditsEarned / TermCreditsAttempted * 100
	 end [Completion Rate]
	from StudentHistory



---
--- Implicit conversion complexities
---

if object_id('evc.DataTypes') is not null
	drop table evc.DataTypes

go


select 
	 1.	A 

	,1.*cast(1 as int) B
	,1.*cast(1 as int)/cast(1 as int) C

	,1.*cast(100 as int) D
	,1.*cast(100 as int)/cast(100 as int) E

	,1.*cast(1 as varchar(50)) F
	,1.*cast(1 as varchar(50))/cast(1 as varchar(50)) G
       
	,1.*cast(9 as varchar(50)) H

	into evc.DataTypes

go

select 
	 COLUMN_NAME
	,DATA_TYPE
	,NUMERIC_PRECISION
	,NUMERIC_SCALE
	from INFORMATION_SCHEMA.COLUMNS
	where TABLE_SCHEMA='evc' and TABLE_NAME='DataTypes'

go

select 	1.*cast(10 as varchar(50)) I
go


--
-- Explicit CAST
--

select 
	 TermCreditsEarned
	,TermCreditsAttempted
	,case when TermCreditsAttempted > 0
		then cast(TermCreditsEarned as float) / cast(TermCreditsAttempted as float) * 100
	 end [Completion Rate]
	from StudentHistory


---
--- convert datetime to string, and string to datetime
---

-- see https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql for list of all styles
select getdate()
	,convert(varchar(50), getdate(), 100)
	,convert(varchar(50), getdate(), 1)
	,convert(varchar(50), getdate(), 101)


select cast('20170510' as datetime)


--
-- date math
--

select dateadd(yy, 10, getdate())

select datediff(dd, '19650926', getdate())

select
	 StudentId, FirstName, LastName, Birthdate
	,datepart(dy, Birthdate)
	,datepart(dy, getdate())
	,case
		when datepart(DY, Birthdate) > datepart(DY, getdate())
			then datediff(YYYY, Birthdate, getdate()) - 1
		else datediff(YYYY, Birthdate, getdate())
	end as Age
	from Student

---
--- String Functions
---

select 
	rtrim('Callahan ') + ', Ed'

select 
	 len('Callahan, Ed')

select
	CHARINDEX(',', 'Callahan, Ed')

select
	substring('Callahan, Ed', 1, CHARINDEX(',', 'Callahan, Ed')-1)

select
	 reverse('Callahan, Ed')
	,substring(reverse('Callahan, Ed'), 1, CHARINDEX(',', reverse('Callahan, Ed'))-1)
	,ltrim(reverse(substring(reverse('Callahan, Ed'), 1, CHARINDEX(',', reverse('Callahan, Ed'))-1)))


---
--- Numeric functions
---

select
	 HS_GPA
	,cast(HS_GPA as int)
	,floor(HS_GPA)
	,round(HS_GPA, 0)
	,round(HS_GPA, 1)
	from Student