Temp Tables and functions

--temp tables and functions
Use Community_Assist

Create table #tempPerson
(
   PersonLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

insert into #TempPerson(PersonlastName, PersonFIrstName, PersonEmail)
Select PersonLastName, PersonFirstname, PersonEmail from Person

Select * from #tempPerson

Create table ##TempEmployee
(
  EmployeeKey int,  
  PersonKey int, 
  EmployeeHireDate date, 
  EmployeeAnnualSalary money
)

Insert into ##TempEmployee(EmployeeKey, PersonKey, 
EmployeeHireDate, EmployeeAnnualSalary)
Select * from Employee

Select * from ##TempEmployee
go
--functions
Create function fx_Cube
(@number int)
returns int
As
Begin
   Declare @cube int
   Set @cube = @number * @number * @number
   return @cube
End
Go
Select dbo.fx_Cube(5) as Cubed
Go
create function fx_DonationPercent
(@amount money, @percentage decimal(5,2))
returns money --set return type
As
Begin --begin function
 if @percentage > 1
  Begin --begin if
  set @percentage = @percentage /100
 end --end if
 Declare @percent money
 set @Percent=@amount * @percentage
 return @percent
End --end function

go
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, 85) charity,
dbo.fx_DonationPercent(donationAmount, .15) Overhead
From Donation

Select sum(donationAmount) Total, 
sum(dbo.fx_DonationPercent(donationAmount, 85)) charity,
sum(dbo.fx_DonationPercent(donationAmount, .15)) Overhead
From Donation

go

Alter function fx_Address
(@Apt nvarchar(255),
@Street nvarchar(255),
@City nvarchar(255),
@State nchar(255),
@Zip nchar(255))
Returns nvarchar(255)
As
Begin
 Declare @address nvarchar(255)
 if @Apt is null 
  Begin
    set @Address=@street + ', ' +@City + ', ' + @State + ' ' + @Zip
  end
 else
  Begin
    set @Address=@Apt + ', '+ @street + ', ' 
 +@City + ', ' + @State + ' ' + @Zip
  end
  return @address
End
Go
Select PersonFirstName, PersonLastName,
dbo.fx_Address( PersonAddressApt, PersonAddressStreet, 
PersonAddressCity, PersonAddressState, 
PersonAddressZip) [Address]
From Person p
Inner Join PersonAddress pa
on p.PersonKey=pa.PersonKey
go
Create Function fx_Zip
(@Zipper nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @Zip nvarchar(255)
set @Zip = @zipper
Return @Zip
End
go
Select dbo.fx_Zip(PersonAddressZip)
From PersonAddress