Execute the following Microsoft SQL Server T-SQL script in Management Studio 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 AdventureWorks2008;

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

/* ProductName    OrderDate   SalesTotal  SalesCount

Mountain-100 Black, 38  2001/07/28  $47,924.86  23

Mountain-100 Black, 38  2001/07/30  $51,299.85  24

Mountain-100 Black, 38  2001/07/30  $54,674.84  25

Mountain-100 Black, 38  2001/08/09  $194,362.97 94   .....*/

Related article:

http://www.sqlusa.com/articles2005/crossapply/