Did you know that you can use Cross Apply in a SQL Query to join together a SQL Table with a Table-valued Function into a single result set?  Here’s how: I have created a Function that contains several calculations and returns some standard Project Metrics:


CREATE FUNCTION [dbo].[fnGetProjectMetrics]
(
    @ProjectId int
)
RETURNS @results table
(
    [ActivityStartDate] datetime
    , [ActivityEndDate] datetime
    , [BillableHours] money
    , [NonbillHours] money
    , [TotalHours] money
    , [TotalRevenue] money
    , [AverageRate] money
    , [EffectiveRate] money
)

I can include the results of that function in my SQL Query:


SELECT
    [Project].[Id]
    , [Project].[Client]
    , [Project].[Name]
    , [ProjectMetrics].[ActivityStartDate]
    , [ProjectMetrics].[ActivityEndDate]
    , [ProjectMetrics].[BillableHours]
    , [ProjectMetrics].[NonbillHours]
    , [ProjectMetrics].[TotalHours]
    , [ProjectMetrics].[TotalRevenue]
    , [ProjectMetrics].[AverageRate]
    , [ProjectMetrics].[EffectiveRate]
FROM
    [dbo].[Project]
    CROSS APPLY [dbo].[fnGetProjectMetrics]([Project].[ProjectId) AS [ProjectMetrics]

Advertisements