UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL
Calculates the expiration date for a membership.
Return
| Return Type |
|---|
| datetime |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MEMBERSHIPLEVELID | uniqueidentifier | IN | |
| @TERMID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN |
Definition
Copy
CREATE function dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL
(
@MEMBERSHIPLEVELID uniqueidentifier,
@TERMID uniqueidentifier,
@STARTDATE datetime
)
returns datetime
as
begin
declare @MEMBERSHIPPROGRAMID uniqueidentifier
declare @CUTOFFDAY tinyint
declare @CUTOFFDATEFORYEAR char(4)
declare @EXPIRATIONDATE datetime
declare @EXPIRESONCODE tinyint
declare @MONTH nvarchar(2)
declare @DAY nvarchar(2)
declare @OBTAINLEVELCODE tinyint
declare @CONTRIBUTIONBASEDEXPIRESONCODE tinyint
select
@CUTOFFDAY = MP.CUTOFFDAY,
@CUTOFFDATEFORYEAR = MP.CUTOFFDATEFORYEAR,
@EXPIRESONCODE = MP.EXPIRESONCODE,
@MEMBERSHIPPROGRAMID = MP.ID,
@OBTAINLEVELCODE = ML.OBTAINLEVELCODE,
@CONTRIBUTIONBASEDEXPIRESONCODE = coalesce(MPC.WHATDATETOCALCULATEEXPIRATIONDATECODE, 0)
from dbo.MEMBERSHIPLEVEL ML
inner join dbo.MEMBERSHIPPROGRAM MP
left outer join dbo.MEMBERSHIPPROGRAMCONTRIBUTION MPC
on MPC.ID = MP.ID
on MP.ID = ML.MEMBERSHIPPROGRAMID
where ML.ID = @MEMBERSHIPLEVELID;
declare @TERMTIMELENGTH int
declare @TERMLENGTHCODE tinyint
select
@TERMTIMELENGTH = TERMTIMELENGTH,
@TERMLENGTHCODE = TERMLENGTHCODE
from dbo.MEMBERSHIPLEVELTERM
where ID = @TERMID;
if @OBTAINLEVELCODE = 1
begin
if @CONTRIBUTIONBASEDEXPIRESONCODE = 0
begin
if @TERMLENGTHCODE = 0
set @EXPIRATIONDATE = dateadd(month, @TERMTIMELENGTH, @STARTDATE)
else
set @EXPIRATIONDATE = dateadd(year, @TERMTIMELENGTH, @STARTDATE)
end;
else
begin
if @TERMLENGTHCODE = 0
begin
set @STARTDATE = dateadd(month, @TERMTIMELENGTH, @STARTDATE)
end;
else
begin
set @STARTDATE = dateadd(year, @TERMTIMELENGTH, @STARTDATE)
end;
-- When using cut off day and the day from transaction falls before it, use
-- the month prior to the month of the transaction date.
if (@CUTOFFDAY > 0) And ( datepart(day, @STARTDATE) < @CUTOFFDAY)
begin
--last day of month
set @EXPIRATIONDATE = dateadd(day, -1, cast(datepart(month, @STARTDATE) as nvarchar(2)) + '/01/' + cast(datepart(year, @STARTDATE) as nvarchar(4)))
end;
-- Otherwise use the last day of the current month
else
begin
set @STARTDATE = dateadd(month, 1, @STARTDATE)
set @EXPIRATIONDATE = dateadd(day, -1, cast(datepart(month, @STARTDATE) as nvarchar(2)) + '/01/' + cast(datepart(year, @STARTDATE) as nvarchar(4)))
end;
end;
end;
else
begin
-- begin expiration date calculation
-- specific date
if @EXPIRESONCODE = 4
begin
declare @DATESTRING as nvarchar(4)
declare @DATEINT as smallint
-- Turning start date into month day.
set @DATESTRING = convert(nvarchar(2), month(@STARTDATE)) +
case
when day(@STARTDATE) < 10 then '0' + convert(nvarchar(2), day(@STARTDATE))
else convert(nvarchar(2), day(@STARTDATE))
end;
set @DATEINT = convert(smallint, @DATESTRING);
-- Load the expiration dates for this program in a table
-- with their effective push dates
-- (PUSHNEXTDATE if defined, the next EXPIRATIONDATE if not)
declare @DATETABLE as table (
EXPDATE nvarchar(4),
PUSHNEXTDATE nvarchar(4)
);
declare @EXPDATE as nvarchar(4)
declare @PUSHNEXTDATE as nvarchar(4)
insert into @DATETABLE (EXPDATE, PUSHNEXTDATE)
select
MPD.EXPIRATIONDATE,
case
when MPD.PUSHNEXTDATE is null or MPD.PUSHNEXTDATE = '0000' then (
coalesce(
( select top(1)
MPD2.EXPIRATIONDATE
from dbo.MEMBERSHIPPROGRAMENDDATE as MPD2
where
MPD2.EXPIRATIONDATE > MPD.EXPIRATIONDATE
and MPD2.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID ),
( select top(1)
MPD2.EXPIRATIONDATE
from dbo.MEMBERSHIPPROGRAMENDDATE as MPD2
where MPD2.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID )
)
)
else
MPD.PUSHNEXTDATE
end
from
dbo.MEMBERSHIPPROGRAMENDDATE as MPD
where
MPD.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
-- Get the valid expiration date by choosing the next push date
select top(1)
@EXPDATE = EXPDATE,
@PUSHNEXTDATE = PUSHNEXTDATE
from
@DATETABLE
where
convert(smallint, PUSHNEXTDATE) > @DATEINT
order by
PUSHNEXTDATE
asc
declare @CARRYOVER bit = 0;
if @EXPDATE is null
select top(1)
@EXPDATE = EXPDATE,
@PUSHNEXTDATE = PUSHNEXTDATE,
@CARRYOVER = 1
from
@DATETABLE
order by
PUSHNEXTDATE
asc
-- Construct the expiration date
set @MONTH = convert(smallint, substring(@EXPDATE, 1, 2))
set @DAY = convert(smallint, substring(@EXPDATE, 3, 2))
set @EXPIRATIONDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@STARTDATE, 0)
set @EXPIRATIONDATE = dateadd(month, @MONTH - 1, @EXPIRATIONDATE)
set @EXPIRATIONDATE = dateadd(day, @DAY - 1, @EXPIRATIONDATE)
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @EXPIRATIONDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @EXPIRATIONDATE)
end
if @CARRYOVER = 1
begin
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, 1, @EXPIRATIONDATE)
when 0 then dateadd(month, 1, @EXPIRATIONDATE)
end
end
if convert(smallint, @PUSHNEXTDATE) < convert(smallint, @EXPDATE)
begin
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, -1, @EXPIRATIONDATE)
when 0 then dateadd(month, -1, @EXPIRATIONDATE)
end
end
end;
-- End of fiscal year
else if @EXPIRESONCODE = 3
begin
set @MONTH = substring(@CUTOFFDATEFORYEAR, 1, 2);
set @DAY = substring(@CUTOFFDATEFORYEAR, 3, 2);
declare @FISCALDATE datetime;
set @FISCALDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@STARTDATE, 1);
set @FISCALDATE = dateadd(year, -1, @FISCALDATE);
if month(@STARTDATE) < @MONTH or (month(@STARTDATE) = @MONTH and day(@STARTDATE) < @DAY)
begin
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @FISCALDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @FISCALDATE)
end
end
else
begin
set @FISCALDATE = dateadd(year,1,@FISCALDATE)
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @FISCALDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @FISCALDATE)
end
end
end;
-- End of calendar year
else if @EXPIRESONCODE = 2
begin
set @MONTH = substring(@CUTOFFDATEFORYEAR, 1, 2);
set @DAY = substring(@CUTOFFDATEFORYEAR, 3, 2);
declare @CALENDARDATE datetime;
set @CALENDARDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@STARTDATE, 1);
if month(@STARTDATE) < @MONTH or (month(@STARTDATE) = @MONTH and day(@STARTDATE) < @DAY)
begin
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @CALENDARDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @CALENDARDATE)
end
end
else
begin
set @CALENDARDATE = dateadd(year, 1, @CALENDARDATE);
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @CALENDARDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @CALENDARDATE)
end;
end
end;
-- End of month
else if @EXPIRESONCODE = 1
begin
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @STARTDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @STARTDATE)
end;
if @CUTOFFDAY <= day(@STARTDATE)
begin
set @EXPIRATIONDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@EXPIRATIONDATE, 1);
end
else
begin
set @EXPIRATIONDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@EXPIRATIONDATE, 1);
end
end;
-- Join date
if @EXPIRESONCODE = 0
set @EXPIRATIONDATE = case @TERMLENGTHCODE
when 1 then dateadd(year, @TERMTIMELENGTH, @STARTDATE)
when 0 then dateadd(month, @TERMTIMELENGTH, @STARTDATE)
end;
end;
return dbo.UFN_DATE_GETLATESTTIME(@EXPIRATIONDATE)
end