Queries and Procedures for Community Assist

--community Assist queries and procedures
use CommunityAssist

--Total Donations by year and month
--Total Grants requested amount allocated
--total grants by Service
--How long the employees have worked 
--Total grants per recipient--compared to lifetime maximums
go
create view vw_TotalDonations
AS
Select Year(DonationDate) as [Year],
Month(donationDate) as [Month],
Sum(donationAmount) as [Total]
From Donation
Group by Year(DonationDate), 
Month(donationDate)
go

Select * From vw_TotalDonations
go
Alter view vw_TotalGrantsByService
as
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
group by ServiceName
Go
Create Proc usp_TotalGrantsByService
@serviceName Nvarchar(255)
As
Select ServiceName as [Service],
Sum(GrantAmount) as Requested,
Sum (GrantAllocation) as Allocated,
Sum(GrantAmount)-Sum(GrantAllocation) 
as [Difference]
From CommunityService cs
inner Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Where ServiceName = @ServiceName
group by ServiceName

exec usp_TotalGrantsByService 'food'

Select min(EmployeeHireDate) from Employee
go
Create view vw_EmployeeInfo
As
Select PersonLastName as [Last Name],
PersonFirstName as [First Name],
EmployeeStatus as [Status],
EmployeeMonthlySalary [Monthly Salary],
EmployeeHireDate HireDate,
DateDiff(yy,EmployeeHireDate, GetDate())
As [Years With Charity]
From Person p
Inner join Employee e
on p.PersonKey=e.PersonKey

Select * from vw_EmployeeInfo
order by [Years With Charity] desc
go
create view vw_GrantsAndServiceMaximums
As
Select PersonFirstName [First Name],
PersonLastName [Last Name],
ServiceName [Service],
ServiceLifeTimeMaximum [Maximum],
sum(GrantAllocation) Allocation
From Person p
inner join ServiceGrant sg
on p.personKey=sg.Personkey
inner join communityService cs
on cs.servicekey=sg.servicekey
Where GrantAllocation is not null
And not GrantAllocation =0
group by personFirstname,
personlastname,
ServiceName,
ServiceLifeTimeMaximum

The query for Metro alt ridership and fare totals

use MetroAlt

Select Year(BusScheduleAssignmentDate) as[Year]
,BusRoutezone,
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end
 [Bus Fare],
Count(Riders) as [total Riders],
case 
When Year(BusScheduleAssignmentDate)=2012 then 2.40
When Year(BusScheduleAssignmentDate)=2013 then 3.15
When Year(BusScheduleAssignmentDate)=2014 then 3.25
When Year(BusScheduleAssignmentDate)=2015 
then 3.50
end *  count(riders) as [Total Fares]
from BusRoute br
inner join BusScheduleAssignment bsa
on br.BusRouteKey=bsa.BusRouteKey
inner Join Ridership r
on r.BusScheduleAssigmentKey=
bsa.BusScheduleAssignmentKey
Group by Year(BusScheduleAssignmentDate),
busRouteZone

The query for bus driver pay

Use MetroAlt


Select EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate) [Year],
EmployeeHourlyPayRate,
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) [total Hours],
Sum(DateDiff(hh, BusDriverShiftSTarttime, BusDriverShiftStopTime)) * EmployeeHourlyPayRate [Annual Pay]
From employee e
inner Join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position p
on  p.PositionKey=ep.PositionKey
inner join BusScheduleAssignment bsa
on e.EmployeeKey=bsa.EmployeeKey
inner Join BusDriverShift bs
on bs.BusDriverShiftKey =bsa.BusDriverShiftKey
Where YEar(BusScheduleAssignmentDate)=2013
And e.EmployeeKey=16
Group by  EmployeeLastName,
EmployeefirstName,
PositionName,
YEar(BusScheduleAssignmentDate),
EmployeeHourlyPayRate