Automart Views for Reports

Use Automart
go
--this view returns the count of vehicles serviced and
--the count of unique vehicles serviced at each location
Create view vw_LocationServiceCount
As
Select LocationName [Location], Count(VehicleServiceId) as [Total Services],
Count (distinct VehicleId) as [Unique vehicles]
From customer.Location loc
Inner Join Employee.vehicleService vs
On loc.LocationID=vs.LocationID
Group by LocationName

Go
--creates a view that returns the 2 top best performing locations
--using top is one of the only times you are allowed
--to use order by in a view
Create view vw_TopLocationServiceCount
As
Select top 2 LocationName [Location], Count(VehicleServiceId) as [Total Services],
Count (distinct VehicleId) as [Unique vehicles]
From customer.Location loc
Inner Join Employee.vehicleService vs
On loc.LocationID=vs.LocationID
Group by LocationName
Order by Count(vehicleServiceId) desc


Go

--using the views. Location 

Select * From vw_TopLocationServiceCount 
Select * From vw_LocationServiceCount 

Select top 5 * from Employee.VehicleService order by ServiceDate desc




Go
--this function calculates the amount with discount
--charged for a particular service
Create function fx_GetTotalDue
 (@price money, @discount decimal(3,2))
 returns money
 As
 Begin
 declare @amount money
 
 Set @amount=@Price - @price* @discount;
 

return @amount
 end
 Go
 --this function calculates the tax due on a service
 Create Function fx_CalculateTax
 (@price money, @TaxPercent decimal(3,2))
 returns money
 as
 begin
 return @Price * @taxPercent
 end
 go

 --this query uses the GetTotal due function
 Select ServiceName,ServicePrice, DiscountPercent, dbo.fx_GetTotalDue(ServicePrice, DiscountPercent) as total
 From Customer.AutoService a
 inner join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 go

 --this view presents a summary of  totals for each location
 Create View vw_LocationSummary
 As
 Select LocationName, count(distinct vs.VehicleServiceId) as [Count],
 sum(dbo.fx_GetTotalDue(ServicePrice, DiscountPercent)) as Total
 From Customer.AutoService a
 inner join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 inner Join Employee.VehicleService vs
 on vsd.VehicleServiceID=vs.VehicleServiceID
 inner Join Customer.Location loc
 on loc.LocationID=vs.LocationID
 Group by LocationName

 go

 --this view provides a summary of all 
 --customer services and charges
 --It would need to be used with a where
 --clause
 Create view vw_CustomerSummary
 As
 Select LastName, Firstname,
 LicenseNumber,VehicleMake, VehicleYear,
 ServiceDate, LocationName,
 ServiceName, ServicePrice, DiscountPercent, 
 dbo.fx_GettotalDue(ServicePrice, DiscountPercent) as SubTotal,
 dbo.fx_CalculateTax(ServicePrice, TaxPercent) as Tax,
 dbo.fx_GettotalDue(ServicePrice, DiscountPercent) 
 + dbo.fx_CalculateTax(ServicePrice, TaxPercent) as Total
 from Person p
 inner Join Customer.Vehicle v
 on p.Personkey=v.PersonKey
 inner join Employee.VehicleService vs
 on v.VehicleId =vs.VehicleID
 inner join Employee.VehicleServiceDetail vsd
 on vs.VehicleServiceID=vsd.VehicleServiceID
 inner join Customer.AutoService a
 on a.AutoServiceID =vsd.AutoServiceID
 inner join Customer.Location loc
 on loc.LocationID=vs.LocationID

 Go

 --using the customer view

 select * from vw_CustomerSummary where LicenseNumber='ITC226'
 Select sum(Subtotal) [subtotal], sum(Tax) [Tax],sum(Total)[Total]
 From vw_CustomerSummary
 Where licenseNumber='ITC226'