UFN_REVENUE_GETPREVIOUSTRANSACTIONDATE
Returns the previous 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_GETPREVIOUSTRANSACTIONDATE] (
@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 > 1 and @DAY <= 15
set @RETVAL = dateadd(d, -1 * (@DAY - 1), @RETVAL)
else
begin
if @DAY = 1
set @RETVAL = dateadd(m, -1, @RETVAL)
set @DAY = day(@RETVAL)
set @RETVAL = dateadd(d, -1 * (@DAY - 15), @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