Views for Reports

Use CommunityAssist
go
Create View vw_TotalDonationsByYearMonth
As
Select Year(DonationDate) [Year], 
Month(DonationDate) [Month],
Sum(DonationAmount) [Total Confirmed],
null as [Total Unconfirmed]
From Donation
Where DonationConfirmDate is not null
Group by Year(DonationDate), Month(donationDate)
Union 
Select Year(DonationDate) [Year], 
Month(DonationDate) [Month],
null as [Total Confirmed],
Sum(DonationAmount) [Total UnConfirmed]
From Donation
Where DonationConfirmDate is  null
Group by Year(DonationDate), Month(donationDate)
go


Select * from vw_TotalDonationsByYearMonth
Where [total confirmed] is not null
go
Create view vw_ServiceTotals
As
Select ServiceName, Coalesce (Sum(GrantAmount), 0) as Requested, 
Coalesce(Sum(GrantAllocation),0) as Granted,
Coalesce(Sum(GrantAmount)-Sum(GrantAllocation),0) as Difference
From CommunityService cs
Left Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Group by ServiceName
Go

Create View vw_ServiceTotalsByYear
As
Select Year(GrantDate) as [Year],
ServiceName, Coalesce (Sum(GrantAmount), 0) as Requested, 
Coalesce(Sum(GrantAllocation),0) as Granted,
Coalesce(Sum(GrantAmount)-Sum(GrantAllocation),0) as Difference
From CommunityService cs
Inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Group by Year(GrantDate),ServiceName
Go

Create view vw_GrantsTotalYearAndMonth
As
Select Year(GrantDate) [Year], Month(GrantDate) [Month],
Sum(GrantAmount) Requested, Coalesce(Sum(GrantAllocation),0) [Alloted]
From ServiceGrant
Group by Year(GrantDate), Month(GrantDate)
go

Create view vw_TopTenDonorsbyAmount
As
Select Top 10 PersonLastName, PersonFirstName, PersonUserName, 
Sum(DonationAmount) as Total
From Person p
Inner join Donation d
on p.PersonKey=d.PersonKey
Group by PersonLastName, PersonFirstName, PersonUserName
order by Total desc
Go

Create view vw_TopDonorsByCount
As
Select PersonLastName, PersonFirstName, PersonUserName, 
Count(DonationAmount) as Total
From Person p
Inner join Donation d
on p.PersonKey=d.PersonKey
Group by PersonLastName, PersonFirstName, PersonUserName
having Count(DonationAmount)>1

go
Create view vw_ResponseTimes
As
Select 
Avg(DateDiff(dd,GrantDate,GrantReviewDate) )as Average,
Max(DateDiff(dd,GrantDate,GrantReviewDate) ) as Most,
Min (DateDiff(dd,GrantDate,GrantReviewDate) ) as fewest
From ServiceGrant
Where DateDiff(dd,GrantDate,GrantReviewDate)>0
Go

Create View vw_EmployeeInfo
as
Select PersonLastName, PersonFirstName, PersonUserName,  EmployeeHireDate, 
EmployeeSSNumber, Coalesce(EmployeeDependents,0) as Dependents,  
EmployeeStatus, EmployeeMonthlySalary
From Person p
inner join employee e
on p.PersonKey=e.personkey