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
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);