/*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
Monday, May 21, 2018
Temp Tables and functions
Subscribe to:
Post Comments (Atom)
This comment has been removed by the author.
ReplyDelete