Views and procedures for reports

--report donations 
--Grant requests
--amounts requested vs amount granted
--Employee hr
--Donors
--Grants per type

Use Community_Assist
go
Alter view vw_Donations
As
Select Year(DonationDate) [Year],
Month(DonationDate) [Month],
Sum (DonationAmount) Total
From Donation
group by Year(DonationDate), Month(donationDate)

Select * from vw_Donations
go
Alter view vw_GrantRequests
As
SELECT 
Year(GrantRequestDate) [Year]
, Month(GrantRequestDate) [Month]
, Sum(GrantRequestAmount) Request
, Sum(GrantAllocationAmount) Allocation
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview grev
on gr.GrantRequestKey=grev.GrantRequestKey
group by Year(GrantRequestDate)
, Month(GrantRequestDate)


Select * from vw_GrantRequests

go
Create View vw_GrantTypeTotals
As
Select Year(GrantRequestDate) [Year]
, GrantTypeName
, sum(GrantRequestAmount) Request
, sum(GrantAllocationAmount) Allocation
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview grev
on gr.GrantRequestKey=grev.GrantRequestKey
group by Year(GrantRequestDate)
, GrantTypeName

Select * from vw_GrantTypeTotals

Create View vw_Employees
As
Select PersonLastName [Last Name]
,PersonFirstName [First Name]
,PersonEmail Email
,EmployeeHireDate [Hire Date]
,EmployeeAnnualSalary Salary
,PositionName [Position]
From Person p
inner join Employee e
on p.PersonKey=e.PersonKey
inner join EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Position pos
on ep.PositionKey=pos.PositionKey

Select * from vw_Employees

Use MetroAlt

Select * from RiderShip
Select * from Fare

Create view vw_AnnualRevenues
as
Select Year(BusScheduleAssignmentDate) [Year],
format(Sum(Riders * FareAmount),'$ #,##0.00') TotalFares
From BusScheduleAssignment bsa
inner join Ridership r
on bsa.BusscheduleAssignmentKey=r.[BusScheduleAssigmentKey]
inner join Fare f
on f.FareKey=r.FareKey
Group by Year(BusScheduleAssignmentDate)

Select * from busRoute
go
Create view vw_RevenuesByCity
As
Select Year(BusScheduleAssignmentDate) [Year],
BusRouteZone [City],
format(Sum(Riders * FareAmount),'$ #,##0.00') TotalFares
From BusScheduleAssignment bsa
inner join Ridership r
on bsa.BusscheduleAssignmentKey=r.[BusScheduleAssigmentKey]
inner join Fare f
on f.FareKey=r.FareKey
inner join BusRoute br
on br.BusRouteKey=bsa.BusRouteKey
Group by Year(BusScheduleAssignmentDate),
BusRouteZone
Go
Alter proc usp_BusRoute
@BusKey int
As 
Select distinct bsa.BusKey,BusStopAddress, BusStopCity, BusStopZipcode
From BusStop bs
inner join BusRouteStops brs
on bs.BusStopKey= brs.BusStopKey
inner join BusScheduleAssignment bsa
on bsa.BusRouteKey=brs.BusRouteKey
Where Buskey=@Buskey

exec usp_BusRoute 72