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
where a.Date = '2019-05-15
) and then joined / passed on to the TVF. Well, that's what I want to happen, at least! I'd like to sayFunction_DataSet(a.Date)
within a cross apply, or something like that. – Roberto May 17 '19 at 19:57CROSS APPLY
is the way to go. Have you browsed some of the sample code out there, or the related questions here and on Stack Overflow? – Jon of All Trades May 17 '19 at 20:23