views Procedures and a function

Use Automart
Go
Create schema Corporation
Go
Create view Customer.vw_Services
As
Select ServiceName As [Service Name], 
ServicePrice AS [Price], 
ServiceDescription As [Description]
From customer.AutoService
go
Select * From Customer.vw_Services Where [service name]='Oil Change'
go
Create view Customer.vw_Locations
As
Select LocationName as Name,
LocationAddress as [Address], 
LocationCity as City, 
LocationState as [State], 
LocationZip as ZipCode, 
LocationPhone as Phone
From Customer.Location
go
Select * from Customer.vw_locations
go
Drop procedure Customer.usp_VehicleServiceHistory
go
Alter procedure Customer.usp_VehicleServiceHistory
@PersonKey int
As
Select LicenseNumber as License, 
VehicleMake as Make, 
VehicleYear as [Year], 
LocationName as [Location], 
ServiceDate as [Date], 
ServiceTime as [Time],
ServiceName as [Service],
'$' + Cast(ServicePrice as nvarchar) as [Price],
DiscountPercent, 
TaxPercent,
'$' + Cast(Cast(ServicePrice -(ServicePrice* DiscountPercent) + 
((ServicePrice * DiscountPercent) * TaxPercent) as Decimal(6,2))as 
nvarchar) as ServiceTotal
From Customer.Vehicle  v
inner join Employee.vehicleService vs
on v.VehicleId=vs.VehicleID
inner join Customer.Location loc
on loc.LocationID=vs.LocationID
inner Join Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID
Where personkey=@personkey

execute customer.usp_VehicleServiceHistory 1


Select LicenseNumber,
VehicleMake, 
VehicleYear, 
ServiceDate,
ServiceTime,
sum(ServicePrice -(ServicePrice* DiscountPercent) + 
((ServicePrice * DiscountPercent) * TaxPercent)) as [Total]
From Customer.Vehicle v
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
Where v.VehicleID=1
Group by LicenseNumber, 
VehicleMake, 
VehicleYear, 
ServiceDate,
ServiceTime

Select LicenseNumber, ServicePrice, DiscountPercent, TaxPercent
From Customer.Vehicle v
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
Where ServiceDate='2010-02-22'
go
alter function fx_TotalDue
(@ServicePrice money, 
@DiscountPercent decimal(4,3), 
@TaxPercent decimal(4,3))
returns money
As 
Begin
Declare @Discounted money
Declare @TaxAmount money
if (@DiscountPercent is not null)
Begin
 set @Discounted=@ServicePrice-@ServicePrice* @DiscountPercent
End
 else
 Begin
  Set @Discounted=@ServicePrice
 End
 Set @TaxAmount=@Discounted * @TaxPercent
 return @Discounted + @TaxAmount
End

Select ServiceDate,Sum (dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent))
as Total
From Employee.VehicleService vs
inner join Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID = vsd.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID = vsd.AutoServiceID
Where vs.VehicleID =1
Group by ServiceDate

Select ServiceDate, ServiceName, ServicePrice, DiscountPercent,
TaxPercent,  (dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent))
as Total
From Employee.VehicleService vs
inner join Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID = vsd.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID = vsd.AutoServiceID
Where vs.VehicleID =1