Help for Preparing date schedule.

  • Hi,

    I need the script for the following schedule.

    if the repayment start date = 05-01-2016 and installment=5 then result will be

    05-01-2016

    05-02-2016

    05-03-2016

    05-04-2016

    05-05-2016

    if the repayment start date = 30-01-2016 and installment=5 then result will be

    30-01-2016

    29-02-2016

    30-03-2016

    30-04-2016

    30-05-2016

    if the repayment start date = 31-01-2016 and installment=5 then result will be

    31-01-2016

    29-02-2016

    31-03-2016

    30-04-2016

    31-05-2016

    if the repayment start date = 29-02-2016 and installment=5 then result will be

    29-02-2016

    31-03-2016

    30-04-2016

    31-05-2016

    30-06-2016

    if the repayment start date = 28-02-2015 and installment=5 then result will be

    28-02-2015

    31-03-2015

    30-04-2015

    31-05-2015

    30-06-2015

    I have find some ways but unable to make it.

    Please help me for this.

  • please provide same Temp table and sample data.

    clear give the exactly your expected values.

  • panneermca35 (6/20/2014)


    Hi,

    I need the script for the following schedule.

    if the repayment start date = 05-01-2016 and installment=5 then result will be

    05-01-2016

    05-02-2016

    05-03-2016

    05-04-2016

    05-05-2016

    if the repayment start date = 30-01-2016 and installment=5 then result will be

    30-01-2016

    29-02-2016

    30-03-2016

    30-04-2016

    30-05-2016

    if the repayment start date = 31-01-2016 and installment=5 then result will be

    31-01-2016

    29-02-2016

    31-03-2016

    30-04-2016

    31-05-2016

    if the repayment start date = 29-02-2016 and installment=5 then result will be

    29-02-2016

    31-03-2016

    30-04-2016

    31-05-2016

    30-06-2016

    if the repayment start date = 28-02-2015 and installment=5 then result will be

    28-02-2015

    31-03-2015

    30-04-2015

    31-05-2015

    30-06-2015

    I have find some ways but unable to make it.

    Please help me for this.

    I believe the last two examples are incorrect (especially based on your 2nd example) and didn't code for them. But, you're the one with the need. If the following doesn't do it for you, let me know and we'll swing a change.

    As usual, details are in the comments in the code.

    CREATE FUNCTION dbo.MonthlyInstallmentSchedule

    /**********************************************************************************************************************

    Purpose:

    Given a start date and the number of monthly installments to make where the start date is the date of the first

    monthly installment, return a list of monthly installment dates and the installment number up to 10,000 installments.

    Programmer Notes:

    1. Suitable for use with SQL Server 2005 and up.

    2. @pInstallments must be >= 0 or a TOP error will occur.

    3. Times will be stripped from the @pStartDate.

    4. Works with SET DATEFORMAT but is totally optional.

    5. This is a high-performance iTVF and won't bog a system down like a scalar or mTVF funtion will.

    Usage Examples:

    --===== Basic syntax

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule(@pStartDate,@pInstallments)

    ;

    --===== Working example (with dmy format)

    SET DATEFORMAT dmy --Not required if dmy is the default

    ;

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule('31-01-2016',5)

    ;

    --===== Working example (with mdy format)

    SET DATEFORMAT mdy --Not required if mdy is the default

    ;

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule('01-31-2016',5)

    ;

    -----------------------------------------------------------------------------------------------------------------------

    Revision History:

    Rev 00 - 20 Jun 2014 - Jeff Moden - Initial creation and unit test.

    **********************************************************************************************************************/

    --===== Declare the I/O for the function

    (

    @pStartDate DATETIME

    ,@pInstallments INT

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN WITH

    WholeDate AS (SELECT StartDate = DATEADD(dd,DATEDIFF(dd,'17530101',@pStartDate),'17530101')) --Convert to whole date

    ,E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10 rows

    ,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10 thousand rows

    ,Tally(N) AS (SELECT TOP(@pInstallments) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4) --Create number sequence

    SELECT InstallmentNumber = t.N

    ,InstallmentDate = DATEADD(mm,t.N-1,d.StartDate)

    FROM WholeDate d

    CROSS JOIN Tally t

    ;

    GO

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • kbhanu15 (6/20/2014)


    please provide same Temp table and sample data.

    clear give the exactly your expected values.

    Your heart is definitely in the right place but look back at the problem... The OP gave exactly what the inputs are and what the outputs should be for each of the simple inputs. Since this is supposed to generate data from the inputs, there's no need for readily consumable test data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ah... looking back at the requirements, I get it now. The last two examples are based on the end of the month and that's what you're trying to return for those.

    I'll see what I can do without slowing this code down too much.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I believe this will do it but you should test a broader range of dates to make sure it works. Look for "Rev 01" to see the changes I made.

    drop function dbo.MonthlyInstallmentSchedule

    go

    CREATE FUNCTION dbo.MonthlyInstallmentSchedule

    /**********************************************************************************************************************

    Purpose:

    Given a start date and the number of monthly installments to make where the start date is the date of the first

    monthly installment, return a list of monthly installment dates and the installment number up to 10,000 installments.

    If the start date is the end of the month, then all dates returned will be the end of the month. (Rev 01)

    Programmer Notes:

    1. Suitable for use with SQL Server 2005 and up.

    2. @pInstallments must be >= 0 or a TOP error will occur.

    3. Times will be stripped from the @pStartDate.

    4. Works with SET DATEFORMAT but is totally optional.

    5. This is a high-performance iTVF and won't bog a system down like a scalar or mTVF funtion will.

    Usage Example:

    --===== Basic syntax

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule(@pStartDate,@pInstallments)

    ;

    --===== Working example (with dmy format)

    SET DATEFORMAT dmy --Not required if dmy is the default

    ;

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule('31-01-2016',5)

    ;

    --===== Working example (with mdy format)

    SET DATEFORMAT mdy --Not required if mdy is the default

    ;

    SELECT InstallmentNumber

    ,InstallmentDate

    FROM dbo.MonthlyInstallmentSchedule('02-29-2016',5)

    ;

    -----------------------------------------------------------------------------------------------------------------------

    Revision History:

    Rev 00 - 20 Jun 2014 - Jeff Moden - Initial creation and unit test.

    Rev 01 - 20 Jun 2014 - Jeff Moden - Add end-of-month sensitivity.

    **********************************************************************************************************************/

    --===== Declare the I/O for the function

    (

    @pStartDate DATETIME

    ,@pInstallments INT

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN WITH

    WholeDate AS (SELECT StartDate = DATEADD(dd,DATEDIFF(dd,'1753',@pStartDate),'1753')) --Convert to whole date

    ,DateInfo AS (SELECT StartDate

    ,IsMonthEnd = CASE --(Rev 01)

    WHEN DATEADD(dd,1,StartDate)=DATEADD(mm,DATEDIFF(mm,'1753',StartDate)+1,'1753')

    THEN 1

    ELSE 0

    END

    FROM WholeDate)

    ,E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10 rows

    ,E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10 thousand rows

    ,Tally(N) AS (SELECT TOP(@pInstallments) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4) --Create number sequence

    SELECT InstallmentNumber = t.N

    ,InstallmentDate =

    CASE --If start date is end of month, always use end of month else use exact or closest date. (Rev 01)

    WHEN IsMonthEnd = 1

    THEN DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,'1753',d.StartDate)+t.N,'1753'))

    ELSE DATEADD(mm,t.N-1,d.StartDate)

    END

    FROM DateInfo d

    CROSS JOIN Tally t

    ;

    GO

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Dear Jeff Moden,

    Thank u so much for your solution. i have shared this with my team they also surprised for your solution. Really thank you so much. :Wow:

  • Dear Jeff,

    Thanks for solution.

    I need one more modification.

    Now we prepared the schedule for monthly basis. if i want to generate the schedule in quarterly basis.

    Example: 1

    No of months:5

    Period : Monthly

    Start Date : 01-05-2015

    Schedule :

    01-05-2015

    01-06-2015

    01-07-2015

    01-08-2015

    01-09-2015

    Example: 2

    No of months:5

    Period : Quarterly

    Start Date : 01-05-2015

    Schedule :

    01-05-2015

    01-08-2015

    01-11-2015

    01-02-2016

    01-05-2016

    Kindly help me.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply