Wednesday, April 11, 2018

Functions

Use Community_Assist

--date functions
Select * from Donation
Select Distinct Year(DonationDate) from Donation
Select Distinct Month(DonationDate) from Donation
Select Distinct Day(DonationDate) from Donation

Select Distinct Year(DonationDate) [Year], Month(donationDate)[Month], 
Day(DonationDate) [Day] from Donation

Select Distinct datepart(hour,DonationDate) from donation
Select GetDate()
Select DateAdd(dd,30,GetDate()) [Month from now]
Select DateDiff(Month, GetDate(), '4/11/2019') [Year from now]
--format
select DonationDate, format(DonationAmount,'$#,##0.00') as Amount from Donation
select * from Contact
Select format(Cast(ContactNumber as bigint),'(000)000-0000') from contact
Select '(' + substring(contactnumber,1,3) + ')' + SUBSTRING(ContactNumber,4,3) 
+ '-' + substring(contactnumber,7,4) as phone from Contact

-- math
Select Donationkey, format(DonationAmount,'$#,##0.00') Amount, format(donationAmount * .30, '$#,##0.00') as maintenance,
format(donationAmount * .70,'$#,##0.00') as charity from donation
--+, -, *, /, % modulus
--Aggregate functions
Select Max(donationAmount) as biggest from Donation
Select Min(donationAmount)  as smallest from Donation
Select Sum(donationAmount) total from Donation
Select Avg(donationAmount) Average from Donation
Select Count(donationAmount) as [How many] from Donation

Select Year(DonationDate) [Year], 
format(Sum(donationAmount),'$#,##0.00') total
From Donation
Group By Year(donationDate)
Order by Year(DonationDate) 

Select Year(DonationDate) [Year], Month(donationDate) [Month], 
format(Sum(donationAmount),'$#,##0.00') total
From Donation
Group By Year(donationDate), Month(DonationDate)
Order by Year(DonationDate) desc, Month(donationDate)
Select Year(DonationDate) [Year],
Case Month(donationDate)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
When 8 then 'Aug'
When 9 then 'Sep'
When 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
End as [Month],
DonationAmount
from Donation

Select Year(DonationDate) [Year],
Datename(Month,donationDate) [Month],
DonationAmount
from Donation

Select name from sys.Databases
Select * from sys.Tables
select name from sys.columns where OBJECT_ID=805577908

No comments:

Post a Comment