alexbancheva

Task12_Cost of Order_Exam27June2020

Feb 10th, 2021
621
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Task 12 Cost of Order
  2. CREATE FUNCTION udf_GetCost(@JobId INT)
  3. RETURNS DECIMAL(16, 2) AS
  4. BEGIN
  5.     DECLARE @Total DECIMAL(16, 2)
  6.     SELECT @Total = SUM(PartsPrice) FROM
  7.     (
  8.         SELECT ISNULL(SUM(p.Price * op.Quantity), 0) AS PartsPrice FROM Orders AS o
  9.         JOIN OrderParts AS op
  10.         ON o.OrderId = op.OrderId
  11.         JOIN Parts AS p
  12.         ON op.PartId = p.PartId
  13.         WHERE o.JobId = @JobId
  14.     ) AS t
  15.     RETURN @Total
  16. END
  17. GO
RAW Paste Data