0

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
  • 1
    Is the date for the TVF fixed, or does it come from some value in one of the other tables? – Jon of All Trades May 17 at 18:57
  • @JonofAllTrades - the data comes a value in the other tables. It gets specified in the query (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 say Function_DataSet(a.Date) within a cross apply, or something like that. – Roberto May 17 at 19:57
  • K, then a CROSS 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 at 20:23
  • Is this a small operation (that is, 1-1 for the full query with the TVF? Or are you planning on making this return hundreds or thousands of rows? – clifton_h May 17 at 20:45
  • @ Jon of All Trades - yes, I have. I can get the cross apply to work partly, but not all the way. Seems that the second cross apply (for IsProductEligible = 'FALSE') is especially problematic. Many products do not have anything with this condition, so they get dropped (acts like an inner join, when I want it to act like a left join). I am new to SQL Server syntax, so I am sure it's just that I haven't figured out the right formulation. Thought I'd ask here as I am sure this sort of query is routine for many of you. – Roberto May 18 at 2:53
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.