+ 2

User defined functions

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

9th Mar 2018, 2:01 PM
Cassie
6 ответов
+ 5
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.
9th Mar 2018, 4:03 PM
Ipang
+ 5
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?
9th Mar 2018, 5:54 PM
Ipang
+ 4
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.
9th Mar 2018, 2:37 PM
Ipang
+ 1
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
9th Mar 2018, 3:37 PM
Cassie
+ 1
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
9th Mar 2018, 5:33 PM
Cassie
+ 1
Oh no it’s okay, thanks for all your help! I’ll run it now and see what happens
9th Mar 2018, 6:12 PM
Cassie