UFN_REVENUE_GETNEXTTRANSACTIONDATE
Returns the Next transaction date for a frequency and date.
Return
| Return Type |
|---|
| datetime |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FREQUENCY | int | IN | |
| @NEXTSCHEDULEDDATE | datetime | IN |
Definition
Copy
CREATE function [dbo].[UFN_REVENUE_GETNEXTTRANSACTIONDATE] (
@FREQUENCY INT,
@NEXTSCHEDULEDDATE datetime)
returns datetime
begin
declare @RETVAL datetime
declare @DAY int
set @RETVAL = @NEXTSCHEDULEDDATE
while @RETVAL <= @NEXTSCHEDULEDDATE
begin
if @FREQUENCY = 0 --ANNUALLY
set @RETVAL = dateadd(yyyy, 1, @RETVAL)
if @FREQUENCY = 1 --SEMI-ANNUALLY
set @RETVAL = dateadd(m, 6, @RETVAL)
if @FREQUENCY = 2 --QUARTYERLY
set @RETVAL = dateadd(m, 3, @RETVAL)
if @FREQUENCY = 3 --MONTHLY
set @RETVAL = dateadd(m, 1, @RETVAL)
if @FREQUENCY = 6 --BIMONTHLY
set @RETVAL = dateadd(m, 2, @RETVAL)
if @FREQUENCY = 7 --SEMI-MONTHLY
begin
set @DAY = Day(@RETVAL)
if @DAY < 15
set @RETVAL = dateadd(d, 15 - @DAY, @RETVAL)
else
begin
set @RETVAL = dateadd(m, 1, @RETVAL)
set @DAY = Day(@RETVAL)
set @RETVAL = dateadd(d, (-1 * @DAY) + 1, @RETVAL)
end
end
if @FREQUENCY = 8 --BIWEEKLY
set @RETVAL = dateadd(d, 14, @RETVAL)
if @FREQUENCY = 9 --WEEKLY
set @RETVAL = dateadd(d, 7, @RETVAL)
end
return @RETVAL
end