Scheduling future due dates

Transact-SQL
1

I have a function that schedules future due dates at intervals based on a specific start date. I had help with this right here in this forum many (10?) years ago, but I admit, I never quite got the modulus logic involved. In any case, the intervals have changed, and now my function is not working properly.

Here is the function:
CREATE FUNCTION [dbo].[fnDueDate]
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)

RETURNS DATETIME

BEGIN
DECLARE @Temp DATETIME
SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)

RETURN @Temp
END

Basically this function, given a start date (@DOP), a month interval(@Interval), and an effective date (usually getdate()) should output the next scheduled date based on the monthly interval. Basically it gives you a date @interval months from the start date based on the effective (current) date (@thisdate).
SELECT dbo.fnDueDate
(
'2020-01-01',
3,
'2020-01-26'
)
yields
2020-04-25 (@interval months from the start date)
This is correct!

However, the due date interval is now only one month, not three, and the function fails with a 1 in the interval parameter, so

SELECT dbo.fnDueDate
(
'2020-01-01',
1,
'2020-01-26'
)
yields 2020-01-01, which is the start date, not 1 month later.

Any ideas? Anyone?

2

What is the use of the 3rd parameter? Interval to me implies possibly many in the final result set.

4

hi

i am trying to run your function !!! i am getting different results !!! WHY ???

DROP FUNCTION [dbo].[fnDueDate]
GO 

CREATE FUNCTION [dbo].[fnDueDate](@DOP AS DATETIME,@INTERVAL AS TINYINT,@ThisDate DATETIME)
	RETURNS DATETIME
BEGIN
	DECLARE @Temp DATETIME
	SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)

	RETURN @Temp
END
go

SELECT dbo.fnDueDate('2020-01-01',3,'2020-01-26')
GO

image

5

ALSO

what is it that you are trying to do ??? please tell

maybe there is a simpler shorter way of getting the answer !!

6

The ultimate goal here is to show the next due date of the report. That is the point of the third parameter, the effective date of the report. An example would be: The child is placed on the 10th of January. The initial report is due on the 20th (10 days later), the next one would be due on February 10th, the one after that March 10, and so on.
So given a date (@DOP), add 10 days for the next one (the function doesn't do this part yet; it needs a CASE statement for that) and then the next interval would be @DOP + 30days (or one month), then + 60 days, etc. It should show the next due date after the current date (@Thisdate).

So given January 27 as @DOP, 1 as @interval, and Feb 26 as @ThisDate, it should return Feb 27. Note that I'm using one month and 30 days interchangeably here, as there is still some disagreement about whether we should use 1 month (simple) or 30 days (less simple) for the due dates. I'll take it either way if it works!

I hope that is clearer.

7

This seems to work!
CREATE FUNCTION [dbo].[fnDueDate3]
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE @Temp DATETIME
SELECT @Temp = CASE WHEN @ThisDate< DATEADD(day,10,@DOP) THEN DATEADD(day,10,@DOP) ELSE DATEADD(DAY, (@Interval - DATEDIFF(DAY, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(DAY, @dop, @ThisDate), @dop) END
RETURN @Temp
END

8

hi

HOW ABOUT A TALLY TABLE APPROACH !!!!

9

PLEASE SEE I TRIED TO USE tally table APPROACH

DECLARE @DOZ      AS DATETIME,
		@INTERVAL AS TINYINT,
		@ThisDate AS DATETIME

SET @DOP      = '2019-10-09'
SET @INTERVAL = 1
SET @ThisDate = '2020-09-23'

;WITH TALLY_CTE AS 
(
	SELECT N=number	FROM master..spt_values	WHERE type = 'P'
)
SELECT 
   TOP 1 DATEADD(MONTH,@INTERVAL*N,@DOP) 
FROM 
  TALLY_CTE 
WHERE 
  DATEADD(MONTH,@INTERVAL*N,@DOP) > @ThisDate
GO

image

10

Thanks Harish! I'll have to read up on those tally tables.