0

I have a scalar function which I am converting to TVF.

In the scalar function I have the below while statement:

WHILE @ReviewDueDate < getdate() 
    SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

When I put that in the TVF, I use CROSS APPLY (got some help in converting) and it gets converted to the below code:

CROSS APPLY (
    VALUES(DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.ReviewDueDate AS TIME), 1, 0) + DATEDIFF(DAY, CA1.ReviewDueDate, GETDATE()) ) / 30.0), CA1.ReviewDueDate))
) CA2(ReviewDueDate)

I don't clearly understand the conversion part.
I understand that when @ReviewDate < getdate(), 30 days needs to be added to the @ReviewDate.
I am kind of lost when I try to understand the converted CROSS APPLY code.

Now the hard part for me is:
I have two more while loops as below:

WHILE @ReviewDueDate < getdate() 
    SET @ReviewDueDate = DATEADD(Month, 6, @ReviewDueDate)

WHILE @ReviewDueDate < getdate() 
    SET @ReviewDueDate = DATEADD(YEAR, 1, @ReviewDueDate)

So I have to convert it to use it in CROSS APPLY.

I am not clear how the find while loop is converted, so I am struggling with the other two.
Any help in explaining the first conversion is appreciated. If someone can help converting the other two, that would be helpful too.

GSerg
  • 76,472
  • 17
  • 159
  • 346
user9516827
  • 141
  • 1
  • 10

1 Answers1

1

You don't need the cross apply per se. Apparently it was simply used to be able to refer to the calculated expression from multiple places.

The formula in the cross apply directly calculates how many 30-days chunks you need to add to @ReviewDueDate so that it exceeds getdate():

  • Take the day difference in days between @ReviewDueDate and today.
  • If time of day of @ReviewDueDate is greater than time of day of today, add one day to that.
  • Divide the resulting number of days by 30.0 to make sure it's not an integer division.
  • Round it up. That is the number of 30-day chunks you need to add.
  • Multiply it by 30 to get the number of days.

This does not nicely translate to non-day intervals such as months or years. For example, this:

DATEADD(month, 6 * CEILING(( IIF(CAST(getdate() AS TIME) > CAST(CA1.ReviewDueDate AS TIME), 1, 0) + DATEDIFF(month, CA1.ReviewDueDate, getdate()) ) / 6.0), CA1.ReviewDueDate);

would be wrong, as it would return the CA1.ReviewDueDate unmodified if today's date was in the same month and the CA1.ReviewDueDates time of date was not greater than today's time of day.

So you need to adjust for the difference in days in addition to months/years:

DATEADD(
  month,
  6 * CEILING(  (IIF(  (day(getdate()) > day(CA1.ReviewDueDate) or CAST(getdate() AS TIME) > CAST(CA1.ReviewDueDate AS TIME)), 1, 0) + DATEDIFF(month, CA1.ReviewDueDate, getdate())) / 6.0  ),
  CA1.ReviewDueDate
)

DATEADD(
  year,
  1 * CEILING(  (IIF(  (month(getdate()) > month(CA1.ReviewDueDate) or day(getdate()) > day(CA1.ReviewDueDate) or CAST(getdate() AS TIME) > CAST(CA1.ReviewDueDate AS TIME)), 1, 0) + DATEDIFF(year, CA1.ReviewDueDate, getdate())) / 1.0  ),
  CA1.ReviewDueDate
)

All that strictly assumes that CA1.ReviewDueDate is always smaller than getdate(). If it's not, you want to wrap the entire thing into another case when ... end that handles this case and does something different when it's already past today.

GSerg
  • 76,472
  • 17
  • 159
  • 346