Refactor SQL to left join on TVF requiring input variable

1 vote
1 answer
1560 views
sql-server set-returning-functions cross-apply
                          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):  + 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
                        
Asked by Roberto (119 rep)
May 17, 2019, 06:25 PM
Last activity: May 20, 2019, 01:41 AM
Refactor SQL to left join on TVF requiring input variable

Related Questions
