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:

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]