VIEWS AND PROCS

--group by busbarn, count number of busses

/*


The total amount earned by a driver in a year (make it a stored procedure and pass in year and driverKey as parameters)

*/


--A summary of how many buses are assigned to each bus barn
USE MetroAlt
CREATE VIEW vw_BusByBusBarn
AS
SELECT Count(BusKey) [NumberOfBusses], BusBarnKey [BusBarn]
FROM Bus
GROUP BY BusBarnKey

--A schedule for a particular bus (do this as a stored procedure where the buskey can be passed as a parameter. This will simply be a schedule of the stops not the times since somehow that table didn't get populated.)
GO
ALTER PROC usp_BusSchedule
@BusKey int
AS
SELECT b.BusKey [Bus]/*from Bus*/, bsa.BusRouteKey [Route]/*from BusScheduleAssignment*/, BusStopKey [Stop]/*from BusRouteStops*/
FROM Bus b
inner join BusScheduleAssignment bsa on b.BusKey=bsa.BusKey
inner join BusRouteStops brs on brs.BusRouteKey=bsa.BusRouteKey
WHERE b.BusKey=@BusKey

EXEC usp_BusSchedule '1'

--The total revenues per city per year
GO 
CREATE VIEW vw_CityRevenueByYear
AS
SELECT BusStopCity [City], Count(Riders)/*from Ridership*/* Sum(FareAmount)/*from FARE*/ [Revenue], Year(FareImplementationDate)/*from FARE*/ [Year]
FROM BusStop bs
join BusRouteStops brs on bs.BusStopKey=brs.BusStopKey
join BusScheduleAssignment bsa on brs.BusRouteKey=bsa.BusRouteKey
join Ridership r on bsa.BusScheduleAssignmentKey=r.BusScheduleAssigmentKey
join Fare f on r.FareKey=f.FareKey
GROUP BY Year(FareImplementationDate), BusStopCity

Select * from vw_CityRevenueByYear
ORDER BY [Year]

--The total cost of buses by Bus type
GO 
CREATE VIEW vw_CostByBusType
AS 
SELECT Count(BusKey)*BustypePurchasePrice [totalcost], bt.BustypeKey
FROM Bus b
join Bustype bt ON b.BusTypekey=bt.BusTypeKey
GROUP BY bt.BustypeKey, BustypePurchasePrice

--The total revenues per year
GO
CREATE VIEW vw_TotalRevenuesByYear
AS
SELECT Count(Riders)/*from Ridership*/* Sum(FareAmount)/*from FARE*/ [Revenue], Year(FareImplementationDate)/*from FARE*/ [Year]
FROM Ridership r 
join Fare f on r.FareKey=f.FareKey
GROUP BY Year(FareImplementationDate)

--The count of employees by position
GO 
CREATE VIEW vw_EmployeesByPosition
AS
SELECT Count(EmployeeKey) [NumberOfEmployees], PositionKey [PositionKey]
FROM EmployeePosition
GROUP BY PositionKey



--The total amount earned by a driver in a year (make it a stored procedure and pass in year and driverKey as parameters)
GO
ALTER PROC usp_EarnedByDriver
@DriverKey int,
@Year int
AS
SELECT 
--Sum(EmployeeHourlyPayRate * dateDiff(hh,BusDriverShiftStartTime, BusDriverShiftStopTime)) PAY
sum(DATEDIFF(Hour, BusDriverShiftStartTime, BusDriverShiftStopTime)) * EmployeeHourlyPayRate
FROM BusDriverShift bds
join BusScheduleAssignment bsa ON bds.BusDriverShiftKey=bsa.BusDriverShiftKey
join EmployeePosition ep ON bsa.EmployeeKey=ep.EmployeeKey
Where @DriverKey=ep.EmployeeKey and @Year=Year(BusScheduleAssignmentDate) 
Group by EmployeeHourlyPayRate

exec usp_EarnedByDriver
@year=2012,
@DriverKey=1


--The cost of a bus versus the amount of fares earned by that bus across the three years. (Does the bus pay for itself) pass in the buskey as a parameter.
GO
CREATE PROC usp_BusCostVSFaresEarned
@BusKey int
AS
SELECT /*bsa.BusKey, bt.BusTypeKey, */BustypePurchasePrice [Cost], Count(Riders)/*from Ridership*/* Sum(FareAmount)/*from FARE*/ [Revenue], Year(FareImplementationDate)/*from FARE*/ [Year]
FROM Bustype bt
join Bus b ON bt.BusTypekey=b.BusTypeKey
join BusScheduleAssignment bsa ON b.BusKey=bsa.BusKey 
join Ridership r ON bsa.BusScheduleAssignmentKey=r.BusScheduleAssigmentKey
join Fare f ON r.FareKey=f.FareKey
WHERE bsa.BusKey=@BusKey
GROUP BY BustypePurchasePrice, FareimplementationDate

EXEC usp_BusCostVSFaresEarned '5'