Temp tables and Functions

Use Community_Assist

--temp table

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 ##GlobalTempPerson
(
 PersonKey int,
  PersonLastName nvarchar(255),
  PersonFirstName nvarchar(255),
  PersonEmail nvarchar(255)
)

insert into ##GlobalTempPerson(PersonKey, PersonLastName, PersonFirstName, PersonEmail)
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
From Person

Select * from ##GlobalTempPerson

--scalar functions
go
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(7.5) as [cube]

go
Alter function fx_OneLineAddress
(@Apartment nvarchar(255),
@Street  nvarchar(255),
@City  nvarchar(255),
@State nchar(2),
@Zip nchar(9))
returns nvarchar(255)
as
Begin
Declare @address nvarchar(255)
if @Apartment is null
   Begin
     Set @Address=@Street + ', ' + @City + ', ' + @state + ' ' + @zip
   End
else
   Begin
      Set @Address= @Street + ' ' + @Apartment + ', ' + @City + ', ' + @state + ' ' + @zip
   End

return @address
End


Select PersonLastName, dbo.fx_OneLineAddress(PersonAddressApt,
[PersonAddressStreet],[PersonAddressCity],[PersonAddressState],
[PersonAddressZip]) as [Address]
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey


Create Function fx_RequestVsAllocationAmounts
(@Request money, @allocation money)
returns money
As
Begin
return @Request-@Allocation
End

Select gr.GrantRequestKey GrantRequestDate, GrantRequestAmount, 
GrantAllocationAmount, 
dbo.fx_RequestVsAllocationAmounts(GrantRequestAmount, GrantAllocationAmount) as [difference]
From GrantRequest gr
inner join grantReview rev
on gr.GrantRequestKey=rev.GrantRequestKey

Select GrantTypeKey, Year(GrantRequestDate) as [Year], Sum(GrantRequestAmount) as Request, 
sum(GrantAllocationAmount) as Allocated, 
sum(dbo.fx_RequestVsAllocationAmounts(GrantRequestAmount, GrantAllocationAmount)) as [difference]
From GrantRequest gr
inner join grantReview rev
on gr.GrantRequestKey=rev.GrantRequestKey
group by GrantTypeKey, Year(GrantRequestDate)