• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

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

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        
0
stkjobmb001
Asked:
stkjobmb001
1 Solution
 
MikeTooleCommented:
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

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now