Selects 2 functions

Use Community_Assist

--scalar
--aggregate
Select * From Donation
--date time functions
Select Distinct Year(DonationDate) [Year] from Donation
Select Distinct Month(DonationDate) [Month] from Donation
Select Distinct Day (donationDate) [Day] from Donation
Select DatePart(YY, DonationDate) [Year] from donation
Select Distinct DatePart(hour, donationDate) [hour] 
From donation

--casting dates to character for formatting
Select cast(month(DonationDate) as nchar(2))+ '/' 
+ cast(day(DonationDate) as nchar(2))
+ '/'+ cast(Year(DonationDate)as nchar(4)) as [Date] 
From Donation

Select * From Employee
--casting and doing math to get the years
Select cast(dateDiff(mm,min(EmployeeHiredate), 
(max(EmployeeHireDate))) / cast(12 as Decimal(10,2))
as decimal(10,2))
 From Employee

 --years and month
 Select dateDiff(mm,min(EmployeeHiredate), 
max(EmployeeHireDate))/12 as [years],
dateDiff(mm,min(EmployeeHiredate), 
max(EmployeeHireDate))% 12 [Months]
From Employee

--format function (only works on numberic types)
Select EmployeeKey, format(EmployeeAnnualSalary,'$#,##0.00')
From Employee

--without format function
Select EmployeeKey, '$' + cast(EmployeeAnnualSalary as nvarchar(10))
From Employee

--just math
Select 4 * 3 - 2 /5.0

--seeing what a 5% raise would look like
Select EmployeeAnnualSalary, 
EmployeeAnnualSalary * 1.05 as Raise
From Employee

--aggregate functions
Select Sum(donationAmount) as total From Donation
Select Avg(DonationAmount) Average from Donation
Select Count(donationAmount) Number from Donation
Select Max(donationAmount) Maximum from Donation
Select Min(donationAmount) Minimum from Donation

--group by. Any column not a part of an 
--an aggregate function must be included
--in a group by clause
Select Year(DonationDate) [year], 
Month(donationDate) [Month],
Sum(donationAmount) total
From Donation
Group by Year(donationDate), Month(DonationDate)

Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number,
format(Sum(GrantRequestAmount),'$#,##0.00') total
from GrantRequest
Group by Year(GrantRequestDate),
Month(GrantRequestDate)

--if the criteria contains an aggregate function
--You have to use a having clause
--the having clause always follows the group by clause
--you can still use a where for non aggregate criteria
Select Year(GrantRequestDate) [Year],
Month(GrantRequestDate) [Month],
Count(GrantRequestKey) Number,
format(Sum(GrantRequestAmount),'$#,##0.00') total
from GrantRequest
Where Month(GrantRequestDate)=8
Group by Year(GrantRequestDate),
Month(GrantRequestDate)
having Sum(GrantRequestAmount) > 1000

--Examples of two system views
Select * from Sys.Databases
Select * from sys.Tables
use MetroAlt
Use Community_Assist
Select * from sys.Columns where Object_ID=373576369