SQL Scalar Functions

--functions   in line  scalar
use Automart

Select * from Customer.autoservice

--doing math in SQL statements
Select ServiceName, ServicePrice, ServicePrice * .095 as Tax From Customer.AutoService

Select ServiceName, '$' + cast(ServicePrice as nvarchar(6)) as Price, 
'$' + cast (cast(ServicePrice * .095 as decimal(5,2)) as Nvarchar(10)) as Tax
From Customer.Autoservice

--date time functions
Select * From Employee.VehicleService
Select Distinct Month(ServiceDate) as [Month] From Employee.VehicleService
Select Distinct Datepart(M,ServiceDate) as [Month] from Employee.VehicleService
Select Distinct Day(ServiceDate) as [Day] from Employee.VehicleService
Select Distinct Month(ServiceDate) as [month], 
Day(ServiceDate) as [Day] from Employee.VehicleService
Select Distinct Datepart(dd,ServiceDate) [Day] from Employee.VehicleService
Select Distinct Year(ServiceDate) as [Year] from Employee.VehicleService
Select Distinct Datepart(yy,ServiceDate) [Year] from Employee.VehicleService
Select Distinct DatePart(hour,ServiceTime) as [Hour] From Employee.VehicleService
Select DateAdd(dd,150, GetDate())
Select DateAdd(mm,150, GetDate())
Select * from Employee order by HireDate
Select DateDiff(yy,'2/13/2000','4/25/2011')
Select DateDiff(dd,'2/13/2000','4/25/2011')
Select Distinct DateDiff(dd, (select min(HireDate) from Employee), (Select Max(HireDate) from Employee) ) as [difference] From Employee

Use CommunityAssist
Select * From Employee
--substrings to extract values from a character type
--takes three values, the string to parse
--the starting position and the number of characters
--to return
Select substring(EmployeeSSNumber,1,3) + '-' 
+ substring(EmployeeSSNumber, 4,2) + '-' +
substring(EmployeeSSNumber, 6,4) as [Social Security]
From  Employee

Select [GrantNeedExplanation] From ServiceGrant

--char index returns the position of a particular character
--in this case a blank space
Select substring(GrantNeedExplanation, 0, CHARINDEX(' ',GrantNeedExplanation,0 )) [first Word] From ServiceGrant

Select * From PersonAddress

--coalesce substitutes a value for a null--it has to
--be the same type--so if you wamt to substitute
--a word for a number you must first cast the 
--number to a character type like nvarchar
Select Street, Coalesce(Apartment, 'None') Apartment, City, [State]
From PersonAddress

use Automart

--you can do cases in sql
Select ServiceName, ServicePrice,
Case 
    when ServicePrice between 0 and 100
 Then 'Cheap'
 when ServicePrice between 101 and 300
 then 'Moderate'
 when ServicePrice > 300
 then 'Expensive'
end
as Comment
From Customer.AutoService