Solved

# Creating a Payment Schedule Table

Posted on 2014-02-12
235 Views
I would like to be able to create a table of scheduled payments based off data in another table.  The fields I would have available would be:  Payment Start Date, # of Months, and Payment Amount.  So for example, if I had the following:

StartDate     # of Months             Amount
03/01/14               36                     \$250.00

I would want to create  table that returns 36 rows with the start date being incremented for each month.  I was thinking the ROW over command would work to increment the start date, but I'm not sure how to create the number of rows off the value of the field # of Months.
0
Question by:garberj76
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 16

Accepted Solution

Surendra Nath earned 500 total points
ID: 39854485
try the below code

``````declare @T TABLE
(
Number INT
)

;WITH C AS
(
SELECT 0 AS T
UNION ALL
SELECT T+1 FROM C WHERE T< 99
)
INSERT INTO @T
SELECT T FROM C

DECLARE @CurrentTable TABLE
(
StartDate	DATE
,NumberOfMonths INT
,Amount Money
)

INSERT INTO @CurrentTable VALUES('03/01/14',36,250)
select * from @CurrentTable

-- if you want the date to be incremented by month
SELECT DATEADD(mm,number,StartDate) as IncrementedDate,Amount FROM @CurrentTable C
CROSS JOIN @T T
WHERE T.Number < C.NumberOfMonths

-- if you want the date to be incremented by Days
SELECT DATEADD(DD,number,StartDate) as IncrementedDate,Amount FROM @CurrentTable C
CROSS JOIN @T T
WHERE T.Number < C.NumberOfMonths
``````
0

LVL 28

Expert Comment

ID: 39857673
I wrote a script to return a row for every day between two dates - in this case you'd change it to increment months, but it's the same concept:

http://trycatchfinally.net/2012/03/return-a-list-of-all-dates-between-a-start-and-end-date/

In your case, the months version would be:

``````DECLARE @StartDate DATETIME,
@Months INT

SET @StartDate = '2014-03-01'
SET @Months   = 36

;WITH numberlist(number)
AS (SELECT RANK() over(order by c1.object_id,
c1.column_id,
c2.object_id,
c2.column_id)
from sys.columns c1
cross
join sys.columns c2)
SELECT DATEADD(mm, number-1, @StartDate)
FROM numberlist
WHERE number <= @Months
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
How can I get the entire database script? 7 34
Database-Scoped Permissions 2 42
T-SQL: Please describe what a page split is 5 58
SQL Server how to create a DYNAMIC TABLE? 11 51
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Six Sigma Control Plans
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
###### Suggested Courses
Course of the Month3 days, 1 hour left to enroll

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

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