Execute the following script in Query Editor to demonstrate the use of CROSS APPLY for high-end (over $3,000) mountain bike sales analysis. First, a table-valued user-defined function (UDF) is created to serve as the righthand-side of the CROSS APPLY.

USE AdventureWorks

GO

CREATE FUNCTION dbo.fnGetProductSalesRunningTotal

(

      @ProductID INT,

      @SalesOrderID INT

)

RETURNS @SalesRunningTotal TABLE

(

      SalesTotal MONEY,

      SalesCount INT

)

BEGIN

INSERT INTO @SalesRunningTotal(SalesTotal, SalesCount)

SELECT

      SUM(LineTotal),

      SUM(OrderQty)

FROM Sales.SalesOrderDetail

WHERE

      ProductID = @ProductID

  AND SalesOrderID <= @SalesOrderID

RETURN

END

GO

SELECT

      ProductName = p.Name,

      OrderDate = convert(char(10),OrderDate,111),

      SalesTotal = '$'+ convert(varchar,SalesTotal,1),

      SalesCount

FROM Sales.SalesOrderDetail sod

JOIN Production.Product p

ON p.ProductID = sod.ProductID

JOIN Sales.SalesOrderHeader soh

on soh.SalesOrderID = sod.SalesOrderID

CROSS APPLY dbo.fnGetProductSalesRunningTotal(sod.ProductID, sod.SalesOrderID)

WHERE p.Name like '%Mountain%'

and UnitPrice > $3000

ORDER BY p.Name ASC, OrderDate, soh.SalesOrderID