Transferring payment arrangement to 2nd table with expected payment date (SQL & VBA)

Posted on 2009-12-20
Last Modified: 2013-11-27
Use code (not queries to perform the following)

I want to take data from the PAYMENT PLAN table and automatically populate a 2nd table, TRACK PAYMENTS, with data for each expected payment date.  I need to option to EITHER populate future payment dates OR populate back dates in the event that the application is used for a previous arrangment.  There may be several changes in a payment plan over time.

PAYMENT PLAN   (Input data for future)
Amt      Period           Beginning      Ending                              
200       month          01/01/2010     12/31/2015  (5 years)
100       month          01/01/2016      12/31/2016  (1 year)
50          month         01/01/2017       03/31/2017  (3 months)

TRACK PAYMENTS (auto populate table)
Amt              Date Expected
200                   01/01/2010    
200                   02/01/2010            
200                   03/01/2010              
200                   04/01/2010        
200                   05/01/2010        
200                   06/01/2010    .......  12/31/2015

100                    01/01/2016
100                    02/01/2016
100                    03/01/2016
100                    04/31/2016    ......  12/31/2016      
50                       01/01/2017 ...  03/31/2017  

PAYMENT PLAN   (Input data from past)
Amt      Period           Beginning      Ending  
200        month            01/01/2003     12/31/2008
100        month             01/01/2009     12/31/2009

TRACK PAYMENTS (auto populate table)
Amt              Date Expected
200                   01/01/2003    
200                   02/01/2003            
200                   03/01/2003              
200                   04/01/2003              
200                   05/01/2003   .......  12/31/2008

100                    01/01/2009
100                    02/01/2009
100                    03/01/2009
100                    04/31/20109   ......  12/31/2009        
Question by:stkjobmb001
    1 Comment
    LVL 27

    Accepted Solution

    My take would be to use a query for accessing Payments not yet made, rather than populating a table. This would avoid any synchronisation problems when the original agreement changes. But you can use the same query to populate the table if you prefer.

    There are various appraoches, some only working with more recent versions of SQL Server. This one should work for all versions.
    Create a table called Nums with one column N as Integer
    Populate it with the natural integer series,one number per row, 1,2,3,4, etc ... upto a value exceeding the maximum number of periods you expect.

    Just using variables to represent a row of your original table, the attached code produced the following result:
    Amt          DueDate
    100      2016-01-01 00:00:00
    100      2016-02-01 00:00:00
    100      2016-03-01 00:00:00
    100      2016-04-01 00:00:00
    100      2016-05-01 00:00:00
    100      2016-06-01 00:00:00
    100      2016-07-01 00:00:00
    100      2016-08-01 00:00:00
    100      2016-09-01 00:00:00
    100      2016-10-01 00:00:00
    100      2016-11-01 00:00:00
    100      2016-12-01 00:00:00

    declare @Start smalldatetime
    declare @end smalldatetime
    declare @Payment as integer
    select  @Start = cast('2016/01/01' as smalldatetime),
    		@end  = cast('2016/12/31' as smalldatetime),
    		@Payment = 100
    select @Payment as Amt, DATEADD(M, N-1, @start) as DueDate
    From Nums
    Where Nums.n between 1 and  DATEDIFF(m,@start,@end) + 1

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    This collection of functions covers all the normal rounding methods of just about any numeric value.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now