In my never ending quest to completely phase out SQL cursors, I am truly discovering some truly awesome features about SQL Server that I have never looked deeply enough before to consider.
My requirement: I need to determine the total value for a project based on a set of monthly deliverables. These deliverables each contain a start and end date, a monthly revenue value, and a monthly set of hours.
For example:
Deliverable
Monthly Hours
Monthly Revenue
Contract Start
Contract End
Deliverable 1
20
$ 2,000
1/1/2012
10/7/2012
Deliverable 2
80
$ 8,400
10/8/2012
12/31/2012
Deliverable 3
1
$ 500
10/8/2012
10/7/2013
What makes this interesting is that for those months in which the contract either doesn’t start on the 1st or end of the last day of the month, the Monthly Revenue must be prorated out: Total Days/Revenue Per Day
I created the following Table Valued function to handle the calculations:
FUNCTION [dbo].[fnCalculateManagedServicesDeliverableTotals]
(
    @deliverable_id int,
    @published int
)
RETURNS @RtnValue table
(
    deliverable_id int,
    published int,
    total_hours money,
    total_revenue money
)
My goal, as I said, is to calculate the total project value. Before I gave up cursors, I would have simply created a cursor that iterated each project deliverable and updated some variables with the results of my Table Valued function. I wanted to accomplish the same thing WITHOUT USING A CURSOR.
The first thing I did was to create and populate a table variable to hold my project deliverables:
DECLARE @deliverables TABLE
(
    deliverable_id int
)
I then created another table variable to hold my results:
DECLARE @work TABLE
(
    deliverable_id int,
    total_amount money,
    total_hours money
)
This is where it got a little sticky for me. I wanted to insert into my @work table the deliverables from my @deliverables table, along with the results of my Table Valued function.
Initially I tried this and got a SQL syntax error: L
INSERT INTO @work
(
    deliverable_id,
    total_amount,
    total_hours
)
SELECT
    pd.deliverable_id,
    d.total_revenue,
    d.total_hours
FROM
    @deliverables AS pd
    INNERJOIN dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published)AS d
WHERE
    pd.deliverable_id = d.deliverable_id
It turns out that this is entirely possible, as long as you are not passing in a value or variable to the table function as parameter, but NOT a column from the joining table.
SQL Server does have a solution for this called CROSS APPLY. If you use CROSS APPLY for INNER JOINS and OUTER APPLY for LEFT OUTER JOINS, then you have the ability to create a join between two table valued expressions, which in my case is a TABLE VARIABLE and the results of a TABLE VALUED FUNCTION.
INSERT INTO @work
(
    deliverable_id,
    total_amount,
    total_hours
)
SELECT
    pd.deliverable_id,
    d.total_revenue,
    d.total_hours
FROM
    @deliverables AS pd
    CROSSAPPLY dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published)AS d
WHERE
    pd.deliverable_id = d.deliverable_id
Here are my results:
Advertisements