Monday, May 21, 2018

Temp Tables and functions

/*Temporary tables and functions*/
Use Community_Assist
Create table #TempPerson
(
   PersonKey int,
   PersonLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

Insert into #tempPerson(PersonKey,
   PersonLastName ,
   PersonFirstName ,
   PersonEmail)
Select  PersonKey,
   PersonLastName,
   PersonFirstName,
   PersonEmail 
   From Person

   Select * from #TempPerson

   Create Table ##tempEmployee
   (
   EmployeeKey int, 
   PersonKey int, 
   EmployeeHireDate Date, 
   EmployeeAnnualSalary money
   )

   Insert into ##TempEmployee
   Select * from Employee

   Select * from ##TempEmployee

   --functions
   go
   Create function fx_cubed(@number int)
   returns int
   As
   Begin
   Declare @Cube int
   Set @Cube=@number * @number * @number
   return @Cube
   End
   Go
   Select dbo.fx_cubed(3)
   Go
   --75% to charity, 25% to maintenance
   Create function fx_DonationPercents
   (@amount money, @percent decimal(5,2))
   returns money
   As
   Begin--begin function
   Declare @Percentage money
   If @Percent <= 1
   Begin --begin if
     Set @Percentage=@Amount * @percent
   End --end if
   Else
   Begin --begin else
     Declare @percentDivisor decimal(5,2) = 100.00
     Set @percentage=@Amount * (@percent / @PercentDivisor)
   End--end else
   return @percentage
   End --end function
   go

   Select donationAmount, 
   dbo.fx_DonationPercents(DonationAmount, .25) Maintenance,
    dbo.fx_DonationPercents(DonationAmount, 75) Charity
 From Donation

  Select 
   sum(dbo.fx_DonationPercents(DonationAmount, .25)) Maintenance,
   sum (dbo.fx_DonationPercents(DonationAmount, 75)) Charity
 From Donation
go
Create function fx_Address
(@street nvarchar(255),
@City nvarchar(255),
@State nvarchar(255),
@Zip nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @address nvarchar(255)
set @Address = @street + ', ' + @city + ', ' + @state + ' ' + @zip
return @address
End
go
Select PersonLastName, dbo.fx_Address(PersonAddressStreet, 
PersonAddressCity, PersonAddressState, PersonAddressZip) [address]
From Person
inner join PersonAddress
on person.personkey = personaddress.personkey
Select * from GrantRequest
Select * from GrantReview

go
Create function fx_GetAllocationAmount
(@grantKey int)
Returns money
As
Begin
Declare @allocation money
Select @allocation=GrantAllocationAmount from GrantReview 
Where GrantRequestKey = @grantKey
return @allocation
End
Go

Select GrantRequestKey, GrantRequestAmount, 
dbo.fx_GetAllocationAmount(grantRequestKey)Allocation
From GrantRequest 

1 comment: