Did you know that you can use 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:

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