Solved

Creating a Payment Schedule Table

Posted on 2014-02-12
2
233 Views
Last Modified: 2014-02-17
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
Comment
Question by:garberj76
2 Comments
 
LVL 16

Accepted Solution

by:
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

Open in new window

0
 
LVL 28

Expert Comment

by:Ryan McCauley
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

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

679 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