/**
*
*  This function is called to get the date a VanLine Statement period ends
*  based upon the van line group and statement year and number.
*
*  See also the IsVanLineStatementImportable() function which uses similar
*  logic and relies upon this function.
*/
CREATE FUNCTION [dbo].[GetVanLineStatementPeriodEndDate](
    @inVanlineGroupExternalCode varchar(10),
    @inStatementYear int,
    @inStatementNumber int,
    @inDefaultEndDate datetime )
returns datetime as
begin
    declare @outStatementEndDate datetime
    declare @theMonth int
    declare @theDay int
    declare @theYear int

    if( @inVanlineGroupExternalCode = 'SIRVA' )
    begin
        -- Sirva periods end on the 15th and end of the month.  We'll determine the day after
        -- the period ends (which is the 16th or the 1st) and then deduct one day.
        if( @inStatementYear >= 2000 and @inStatementYear <= 9999 and @inStatementNumber >= 1 and @inStatementNumber <= 24 )
        begin
            -- Ensure we don't have out of range values.
            select
                @theYear = @inStatementYear,
                @theMonth = ( @inStatementNumber + 2 ) / 2,
                @theDay = case @inStatementNumber % 2
                    when  1 then 16        -- 16th of the current month
                    else 1                -- 1st day of the following month.
                end
            if( @theMonth >= 13 )
            begin
                -- Handle December -> January
                select
                    @theMonth = 1,
                    @theYear = @theYear + 1
            end
            set @outStatementEndDate = datefromparts( @theYear, @theMonth, @theDay )
            set @outStatementEndDate = dateadd( day, -1, @outStatementEndDate )
        end
    end

    return isnull( @outStatementEndDate, @inDefaultEndDate )
end
GO
GRANT EXECUTE ON  [dbo].[GetVanLineStatementPeriodEndDate] TO [MssExec]
GO