sqlsql-servercross-apply

Conditional CROSS APPLY in SQL Server


I have the following query which works perfectly fine:

Version #1

SELECT 
    t.ScheduleId,t.BaseDate,t.AfterDate,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY 
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, t.AfterDate, 0, 1, NULL, NULL, NULL) fn

Further, I did the following changes to the above query which also works fine:

Version #2

SELECT 
    t.ScheduleId, t.BaseDate, t.AfterDate, t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY 
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, COALESCE(t.PreviousBillingDate, t.BaseDate), 0, 2, NULL, NULL, NULL) fn
WHERE
    fn.RowNumber = 2

The question

Now, based on a certain condition I want to pass different args in the user defined function dbo.fn_ScheduleCalculator, but I am not able to get the syntax right.

Here's what I am trying to achieve:

SELECT 
    t.ScheduleId, t.BaseDate, t.AfterDate, t.memberagreementitemid,  
    fn.ScheduleDate AS NextBillingDate
INTO 
    #Distinct_BillableMemberAgreementItems
FROM 
    Distinct_BillableMemberAgreementItems_CTE t
CROSS APPLY
IF ((COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()) 
    OR (t.BaseDate <> GETDATE() and t.FromBilling = 0))

    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, COALESCE(t.PreviousBillingDate, t.BaseDate), 0, 2, NULL, NULL, NULL) fn
    WHERE
        fn.RowNumber = 2
ELSE
    dbo.fn_ScheduleCalculator(t.ScheduleId, t.BaseDate, t.AfterDate, 0, 1, NULL, NULL, NULL) fn)

I understand that without sharing some data for the above tables it will be hard to suggest the exact syntax on how to achieve what I am looking for, but even if someone can suggest how I can achieve what I am looking for or if any issues in the syntax that you see and can point out will be of great help.

Here:

dbo.fn_ScheduleCalculator: is a user defined function

Distinct_BillableMemberAgreementItems_CTE: is a CTE


Solution

  • You can use a CROSS APPLY with a virtual table to do some calculations, then pass those to the function.

    I don't know the meaning of the calculations, so I've just give them generic names. I also don't know the correctness of your logic, I've just copied what you've shown.

    SELECT 
        t.ScheduleId,
        t.BaseDate,
        t.AfterDate,
        t.memberagreementitemid,  
        fn.ScheduleDate AS NextBillingDate
    INTO 
        #Distinct_BillableMemberAgreementItems
    FROM 
        Distinct_BillableMemberAgreementItems_CTE t
    
    CROSS APPLY (
        SELECT
            CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
                   OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
              THEN ISNULL(t.PreviousBillingDate, t.BaseDate)
              ELSE t.AfterDate
            END,
            CASE WHEN ISNULL(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
                   OR (t.BaseDate <> GETDATE() AND t.FromBilling = 0)
              THEN 2
              ELSE 1
            END
    ) v1(SomeCalculation1, SomeCalculation2)
    
    CROSS APPLY dbo.fn_ScheduleCalculator(
        t.ScheduleId,
        t.BaseDate,
        v1.SomeCalculation1,
        0,
        v2.SomeCalculation2,
        NULL, NULL, NULL
      ) fn
    WHERE
        (v1.SomeCalculation2 = 1 OR fn.RowNumber = 2);