Functions and Views

--serviceprice is in Customer.Autoservice
--Discount percent is in Employee.VehicleService detail
--tax percent is in Employee.VehicleServiceDetail

--maybe three functions
--one for price with discounts for each service,
--one for tax
--one for total
--used with sums

--then a view that shows these

Select * from Customer.AutoService
Select * From Employee.VehicleServiceDetail

Go
Create function Employee.func_PriceWithDiscount
--parameters the user provides
(@ServiceID int, @Discount decimal(3,2))
returns money --datatype returned
As
Begin --begin function
--declare variables
Declare @price money
Declare @ServiceCost money
--Getting the price from the autoservice table
Select @price = ServicePrice from Customer.AutoService
Where AutoServiceID=@ServiceID
--calculating discount

Set @ServiceCost=@price - (@price * @Discount)

Return @ServiceCost
End


--using the Function
Select
vd.VehicleServiceID,ServiceDate,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent))
as [Before Tax]
From Employee.VehicleServiceDetail vd
Inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vd.VehicleServiceID
Where vd.VehicleServiceID=3
Group By vd.VehicleServiceID, ServiceDate

--using the function again
Select *, Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)
as [Before Tax]
From Employee.VehicleServiceDetail
where VehicleServiceID=3

Select * from Customer.AutoService where AutoServiceID=7

Go
--create the tax function
Create Function Employee.GetTax
(@subtotal money, @TaxPercent decimal(3,2))
Returns money
As
Begin
return @subtotal * @TaxPercent
End

Go
--create a view that uses the two functions to return the details
--of a transaction
Create View Employee.PriceDetail
As
Select VehicleServiceID,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) as Subtotal,
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Tax,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID

Go
--create a view that shows the summary of a transaction
Create View Employee.PriceTotal
As
Select VehicleServiceID,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID


--select from the two views
Select * from Employee.PriceDetail where VehicleServiceID=24
Select * From Employee.PriceTotal Where VehicleServiceID=24