HOTSPOT
You are developing an SQL Server database. The database contains two tables and a function that are defined by the following Transact-SQL statements.
You need to create a query to determine the total number of products that are sold each day for the live top-selling products on that particular day.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.
Hot Area:
Mind the error on the picture in the last combo – function takes 1 parameter – not two.
Here is full correct answer:
WITH OrderDates (OrderDate) AS (
SELECT DISTINCT OrderDate FROM [SalesOrderHeader])
SELECT OrderDate, SUM(T.[count]) FROM OrderDates
CROSS APPLY TopSellingProducts(OrderDate) AS T
GROUP BY [OrderDate]
I assume that not visible part of the function is:
SELECT TOP(5) d.ProductID, SUM(d.OrderQty) count
FROM SalesOrderDetail d INNER JOIN SalesOrderHeader h ON d.SalesOrderID=h.SalesOrderID
WHERE h.OrderDate = @date
GROUP BY d.ProductID
ORDER BY SUM(d.OrderQty) DESC