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:
- TABLE_A: Date, LimitName, LimitValue, Limit >> main table that
specify limits I want to check - TABLE_B: Date, LimitName, ProductType (one-to-many) >> helper table that specify which products types map into each limit
- 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
Best Answer
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.