Correlated join with outer fields as parameters

Question

I need a way to join a table with a function's results. I'm not sure if it's possible and somehow I don't think it's a good idea. Let me try and explain the situation.

There is a table [Entities]:

[ID]    [Description]    [kWh]    [kVArh]    [kVAh]    [OfferID]    [CustomOfferID]

Another table [Data]:

[ID]    [Timestamp]    [Value]

And a function:

[Calc] (@offer, @customOffer, @kWh, @kVArh, @kVAh, @dtStart, @dtEnd)

So you can see there are entities, the entities' data (usage) and a function to calculate the cost.

I need to display the usage and cost of multiple entites:

[Description]    [MWh]    [MVA]    [Cost]

[MWh] will be a SUM of all the entity's data over the period; [MVA] will be the MAX of all the data over the period; and [Cost] will SUM the [Cost] field from the sub query (function).

The query I figured would do the jobs looks like this:

SELECT [tc].[ID], [tc].[Desc]
    , SUM([kWh].[Value]) / 1000 AS [MWh]
    , MAX([kVAh].[Value]) / 1000 AS [MVA]
    , SUM([cost].[Cost])
FROM [Tree_Cost] AS [tc]
INNER JOIN [Data] AS [kWh] ON [tc].[kWh] = [kWh].[ID]
INNER JOIN [Data] AS [kVAh] ON [tc].[kVAh] = [kVAh].[ID]
INNER JOIN
(
    SELECT [tc].[ID], [Cost]
    FROM [Calc] ([tc].[Offer_ID], [tc].[OfferCustom_ID], [tc].[kWh], [tc].[KVArh], [tc].[kVAh], @dtStart, @dtEnd)
) AS [cost] ON [tc].[ID] = [cost].[ID]
WHERE [tc].[Type] = 1 AND [tc].[TypeDesc] = 'GF_K_M'
AND [kWh].[Timestamp] BETWEEN @dtStart AND @dtEnd
AND [kVAh].[Timestamp] BETWEEN @dtStart AND @dtEnd
GROUP BY [tc].[ID], [tc].[Desc]

The real problem here is that I need to include the [ID] from the outer query in the result set of the inner query (function) in order to be able to join the two. Then I also need to be able to use the fields from the outer query as arguments for the inner query (function).

This is obviously not the way seeing as the [tc] identifier is not recognized in the inner query. So how am I supposed to accomplish something like this?

---

CREATE FUNCTION [dbo].[Calc]
  ( \@intOffer [int]
  , \@intCustom [int]
  , \@intP [int]
  , \@intQ [int]
  , \@intS [int]
  , \@dtStart [datetime]
  , \@dtEnd [datetime]
  )
RETURNS TABLE
      ( [Entry] [nvarchar](200) NULL
      , [Rate] [float] NULL
      , [Unit] [nvarchar](50) NULL
      , [Reading] [float] NULL
      , [Cost] [float] NULL
      , [DDate] [nvarchar](50) NULL
      )
WITH EXECUTE AS CALLER
  AS EXTERNAL NAME [OfferCalcLite].[UserDefinedFunctions].[SqlArray]

Answer 1

I'm not sure I understand correctly. Perhaps you can completely drop the JOIN:

INNER JOIN
(
    SELECT [tc].[ID], [Cost]
    FROM [Calc] ([tc].[Offer_ID], [tc].[OfferCustom_ID], [tc].[kWh], [tc].[KVArh], [tc].[kVAh], @dtStart, @dtEnd)
) AS [cost] ON [tc].[ID] = [cost].[ID]

and change:

, SUM([cost].[Cost])

into:

, SUM( [Calc] ( [tc].[Offer_ID]
              , [tc].[OfferCustom_ID]
              , [tc].[kWh]
              , [tc].[KVArh]
              , [tc].[kVAh]
              , @dtStart, @dtEnd
              )
     ) AS Cost