Search code examples
sqlsql-serversql-functionnorthwind

SQL Server - creating a function using the northwind database

Before starting I want you to know that I am using the well-known northwind database of microsoft.

So, what I need is to create a function which retrieves me the following result set:

result set

The Query needs to create a function which receives a country name (shipCountry field of table Orders) as an argument, and it has to output the last name of the employees(lastName field of table Employees) and the total amount of the income each employee made by each year(1996,1997,1998) in the country that is received in the input of the function.

When i talk about the total amount of the income they made, im referring to sum up all the unit prices of the orders. (i used the fields: unitPrice, quantity and discount of the table [Order Details])

Here I show u guys the query I made so u can get a better idea of what i am trying to do, my problem is that i do not know where to put the condition of the country name(as i said we just have to show the info based on the country we inserted in the function):

CREATE FUNCTION fn_listAnualAmounts(@country NVARCHAR)
RETURNS TABLE
AS
BEGIN
    DECLARE @anio96 MONEY, @anio97 MONEY, @anio98 MONEY 

    SET @anio96 = (SELECT SUM((od.UnitPrice - od.Discount)*od.quantity) as [Año 1996]
                    FROM [Order Details] od 
                        left join Orders o on o.OrderID = od.OrderID
                        left join Employees e on e.EmployeeID = o.EmployeeID
                    GROUP BY YEAR(o.OrderDate),e.EmployeeID
                    HAVING YEAR(o.OrderDate) = '1996')
    SET @anio97 = (SELECT SUM((od.UnitPrice - od.Discount)*od.quantity) as [Año 1997]
                    FROM [Order Details] od 
                        left join Orders o on o.OrderID = od.OrderID
                        left join Employees e on e.EmployeeID = o.EmployeeID
                    GROUP BY YEAR(o.OrderDate),e.EmployeeID
                    HAVING YEAR(o.OrderDate) = '1997')
    SET @anio98 = (SELECT SUM((od.UnitPrice - od.Discount)*od.quantity) as [Año 1998]
                    FROM [Order Details] od 
                        left join Orders o on o.OrderID = od.OrderID
                        left join Employees e on e.EmployeeID = o.EmployeeID
                    GROUP BY YEAR(o.OrderDate),e.EmployeeID
                    HAVING YEAR(o.OrderDate) = '1998')

    RETURN (SELECT e.LastName, @anio96, @anio97, @anio98
            FROM Employees e)
END
GO

Here You have the database diagram of northwind

enter image description here

Thank you so much you all!

Solution

  • Just use conditional aggregation:

    SELECT e.LastName,
       SUM(CASE WHEN YEAR(o.OrderDate) = 1996
                THEN (od.UnitPrice - od.Discount)*od.quantity
           END) as total_1996
       SUM(CASE WHEN YEAR(o.OrderDate) = 1997
                THEN (od.UnitPrice - od.Discount)*od.quantity
           END) as total_1997,
       SUM(CASE WHEN YEAR(o.OrderDate) = 1998
                THEN (od.UnitPrice - od.Discount)*od.quantity
           END) as total_1998
FROM [Order Details] od LEFT JOIN
     Orders o 
     ON o.OrderID = od.OrderID LEFT JOIN
     Employees e 
     ON e.EmployeeID = o.EmployeeID
GROUP BY e.EmployeeId, e.LastName;