User defined functions

Question

How would I write a user defined function that will receive a productID and integer value representing a calendar year as arguments and which will return the total value of sales for that product in a year. Then I want to use that function to list each product and its sales for the year 1998.

Any ideas will be greatly appreciated

Answer 1

Well, I guess you can start your way by looking up on how to create stored procedures using T-SQL (assumed Ms SQL Server, you mentioned SQL only).

(Edit)
Forgot to put this link, sorry : )

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

I have forgotten the tables in Northwind database, but if you want to get total sales per year I guess you need to join products table with another table which records the sales date/time. Once you got them joined you can easily add WHERE clause to filter data by sales year.

Do you have a sketch of the query to execute to achieve your output? I'd like to see it if it's okay with you.

Answer 2

Oh sorry for delay, I was on something else, anyways, your query looks legit, have you tried running it?

(Edit)
I'm thinking YEAR(OrderDate) just to check the year of the order date, I guess it would be simpler rather than using BETWEEN, your thought?

Answer 3

Can you share your table definition (structure) it would help those who wants to respond in understanding the problem, and the DBMS you are using of course.

Answer 4

I’m using the sample Northwind DB that comes with sql . The products table has 10 attributes ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued

Answer 5

Of course!

So I’m thinking it’s:

CREATE FUNCTION dbo.udf_TotalSales
(@ProductID int)
RETURNS int

AS

BEGIN
DECLARE @Sum int

SET @Sum = (Sum ( UnitPrice * Quantity))
FROM [Order Details]
WHERE ProductID = @ProductID

RETURN @Sum
END

Using the function

SELECT ProductID, ProductName, dbo.udf_TotalSales (ProductID) AS ‘Total Sales’
FROM Products LEFT JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON [Order Details].OrderID = Orders.OrderID
WHERE OrderDate BETWEEN ‘1998-01-01 00:00:00’ AND ‘1998-12-31 23:59:59’

A picture of the northwind database

https://github.com/jpwhite3/northwind-SQLite3

Answer 6

Oh no it’s okay, thanks for all your help! I’ll run it now and see what happens