ISREGULARPAY

Updated 2024-02-13 20:49:55.283000

Syntax

SELECT [westclintech].[wct].[ISREGULARPAY](
  <@IssueDate, datetime,>
 ,<@FirstPaymentDate, datetime,>
 ,<@PmtPerYear, int,>)

Description

Use the scalar function ISREGULARPAY to determine if a date is a regular payment date for a loan given the first payment date, the issue date, and the number of payments per year.

Arguments

@IssueDate

the start or first interest accrual date of the loan. @IssueDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@FirstPaymentDate

the first interest payment date of the loan. @FirstPaymentDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

@PmtPerYear

the number of payments per year. @PmtPerYear is an expression of type int or of a type that can be implicitly converted to int.

Return Type

bit

Remarks

@PmtPerYear must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52 or 365.

Examples

A loan with weekly repayments with the first payment 7 days after the issue date.

SELECT wct.ISREGULARPAY(   '2014-01-13', --@IssueDate

                           '2014-01-20', --@FirstPaymentDate

                           52            --@PmtPerYear

                       ) as IsRegularPay;

This produces the following result.

{"columns":[{"field":"IsRegularPay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IsRegularPay":"1"}]}

The following example uses 2013-12-31 as @IssueDate and then compares all the days in January 2014 (using the SERIESDATE function from the XLeratorDB math library) as @FirstPaymentDate and pivots the results for each possible value of @PmtPerYear .

SELECT FirstPaymentDate,

       [1],

       [2],

       [3],

       [4],

       [6],

       [12],

       [13],

       [24],

       [26],

       [365]

FROM

(

    SELECT x.PmtPerYear,

           CAST(k.seriesvalue as date) as FirstPaymentDate,

           CAST(wct.ISREGULARPAY('2013-12-31', k.seriesValue, x.PmtPerYear) as 

                     float) as RegPay

    FROM

    (

        VALUES

            (1),

            (2),

            (3),

            (4),

            (6),

            (12),

            (13),

            (24),

            (26),

            (52),

            (365)

    ) x (PmtPerYear)

        CROSS APPLY wctMath.wct.SeriesDate('2013-12-31', '2014-01-31', 1, NULL, 

                  NULL) k

) d

PIVOT

(

    SUM(RegPay)

    FOR PmtPeryear IN ([1], [2], [3], [4], [6], [12], [13], [24], [26], [365])

) as P;

This produces the following result.

{"columns":[{"field":"FirstPaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"6","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"12","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"13","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"24","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"26","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"365","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2013-12-31"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"1","FirstPaymentDate":"2014-01-01"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-02"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-03"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-04"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-05"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-06"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-07"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-08"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-09"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-10"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-11"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-12"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-13"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"1","365":"0","FirstPaymentDate":"2014-01-14"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"1","26":"0","365":"0","FirstPaymentDate":"2014-01-15"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-16"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-17"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-18"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-19"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-20"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-21"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-22"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-23"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-24"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-25"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-26"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-27"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"1","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-28"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"0","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-29"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"1","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-30"},{"1":"0","2":"0","3":"0","4":"0","6":"0","12":"1","13":"0","24":"0","26":"0","365":"0","FirstPaymentDate":"2014-01-31"}]}