Refactor SQL to left join on TVF requiring input variable

Question

I would like to join two tables and a table-valued function. The TVF take a date as input variable.

The expected output is: Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible

The Tables:

1. TABLE_A: Date, LimitName, LimitValue, Limit >> main table that specify limits I want to check
2. TABLE_B: Date, LimitName, ProductType (one-to-many) >> helper table that specify which products types map into each limit
3. TABLE_C (TVF): (several identification columns, like ID value, dates) + Date, ProductType, ProductValue, IsProductEligible >> the main product data to be aggregated and mapped into the limits

I am able to get the expected result using a query as below.

However, because the TVF takes a date as input variable, this query requires specifying the date 3 times. I would like to refactor the query to specify the date once, and have that condition applied to the TVF.

Reading up on this, I think that cross apply and outer apply should be able to do this, but I cannot seem to get syntax that works. I end up having to put where conditions in place, which drops rows (works like inner join rather than left join). I would appreciate some direction on how to make this work.

select
  a.Date
, a.LimitName
, sum(c_elig.ProductValue) as ProductValueEligible
, sum(c_non.ProductValue) as ProductValueNonEligible

-- TABLE_A: Date, LimitName, LimitValue, Limit
from Limits a

-- TABLE_B: Date, LimitName, ProductType (one-to-many)
left join LimitsProductTypes b
on b.Date = a.Date
and b.LimitName = a.LimitName

-- TABLE_C (TVF): <identification columns, like ID value, dates> + Date, ProductType, ProductValue, IsProductEligible
left join (
    -- Find eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'TRUE'
    group by Date, ProductType
) c_elig
on c_elig.Date = a.Date
and c_elig.ProductType = b.ProductType

left join (
    -- Find non-eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'FALSE'
    group by Date, ProductType
) c_non
on c_non.Date = a.Date
and c_non.ProductType = b.ProductType

where a.Date = '2019-05-15'   --<------------------------------------------- want to just specify date here

group by a.Date, a.LimitName

Answer 1

Use the OUTER APPLY operator to join your TVF to your tables and still return the NULL records as well.

NOTE: In the below sample your NULL values might be excluded because there is no NULL handling in the WHERE clause, but without sample data or DDL it was difficult to write this portion.

SELECT
    a.[Date],
    a.[LimitName],
    SUM(c_elig.ProductValue) as ProductValueEligible,
    SUM(c_non.ProductValue) as ProductValueNonEligible,
FROM Limits a
LEFT JOIN LimitsProductTypes b ON b.[Date] = a.[Date] AND b.LimitName = a.LimitName
OUTER APPLY Function_DataSet(a.[Date]) c_elig
OUTER APPLY Function_DataSet(a.[Date]) c_non
WHERE a.Date = '2019-05-15'
    AND (c_elig.IsProductEligible = 'TRUE'
    AND c_elig.Date = a.Date
    AND c_elig.ProductType = b.ProductType)
    AND (f2.IsProductEligible = 'FALSE'
    AND c_non.Date = a.Date
    AND c_non.ProductType = b.ProductType)
GROUP BY a.Date, a.LimitName