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.
|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 INNER JOIN 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
@deliverables AS pd
CROSS APPLY dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published) AS d
pd.deliverable_id = d.deliverable_id
Here are my results: