Conditional CROSS APPLY in SQL Server-CodePudding

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

CodePudding user response：

With the clarification on the requirements you need to use two queries and UNION ALL to merge those two queries together. Following the current requirements it would be something like this.

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 
(
    COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
    OR 
    (
        t.BaseDate <> GETDATE() 
        and 
        t.FromBilling = 0
    )
)
AND fn.RowNumber = 2

UNION ALL

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, t.AfterDate, 0, 1, NULL, NULL, NULL) fn
where NOT
(
    COALESCE(t.PreviousBillingDate, t.LastInvoicedDate) = GETDATE()
    OR 
    (
        t.BaseDate <> GETDATE() 
        and 
        t.FromBilling = 0
    )
)

CodePudding user response：

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,
    v.SomeCalculation1,
    0,
    v.SomeCalculation2,
    NULL, NULL, NULL
  ) fn
WHERE
    (v1.SomeCalculation2 = 1 OR fn.RowNumber = 2);