Views for Reports

--List of Donors
--Amount allocated to each grant type
--total donations by year and month
--Total grants by year and month (count, total alloctated)
--Average request per grant type
--total requests vs total allocated (by year month--also by type)

use Community_Assist

Go
Create view vw_DonorContact
As
Select PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
Where Personkey in (Select PersonKey from Donation)

Go
Alter view vw_DonorContactb
As
Select Distinct PersonLastName LastName,
PersonFirstName FirstName,
PersonEmail Email
From person
inner join Donation
on person.PersonKey = Donation.PersonKey
go

Select * from vw_DonorContactb order by LastName

Select * from Donation
go
Create view vw_TotalDonationsByYearMonth
as
Select Year(DonationDate) [Year], 
DateName(month, DonationDate) [MonthName],
format(sum(DonationAmount),'$ #,###.00') Total From Donation
Group by Year(donationdate), DateName(month, DonationDate)
go
Select * from vw_TotalDonationsByYearMonth order by Year

go
Create view vw_TotalGrantsByYearMonth
As
Select Year(GrantReviewDate) [Year],
DateName(month, GrantReviewDate) [Month],
Sum(GrantAllocationamount) Total
From GrantReview
Group by Year(GrantReviewDate), Datename(Month,GrantReviewDate)

go
Create view vw_TotalAllocatedByGrantType
As
Select GrantTypeName, sum(GrantAllocationAmount) Total
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go
Create view vw_CountByGrantType
As
Select GrantTypeName, Count(*) [Count]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName

go

Create view vw_RequestvsAllocationByGrantType
AS
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
Group by GrantTypeName
go
Create proc usp_RequestvsAllocationByGrantType
@GrantTypeName nvarchar(255)
As
Select GrantTypeName, Sum(GrantRequestAmount) RequestAmount,
Sum(GrantAllocationAmount) AllocatedAmount,
Sum(GrantRequestAmount)-Sum(GrantAllocationAmount) [Difference]
From GrantType gt
inner join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
inner join GrantReview grw
on gr.GrantRequestKey=grw.GrantRequestKey
where GrantTypeName=@GrantTypeName
Group by GrantTypeName


exec usp_RequestvsAllocationByGrantType 'Child Care'