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]

Advertisements