DML for monthly schedule - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DML for monthly schedule

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-11-24 : 15:59:49

Guys,

I have to generate monthly payment schedule from the below information

NoofPayments TotalAmt ContractNum startdayofmonth
4 4542 1 4

Based on the above information the schedule should be generated starting 4th day of next month, payment amount rounded off to 2 digits after the decimal

PymntDate PymntAmt
12/4/08 1135.5
01/4/09 1135.5
02/4/09 1135.5
03/4/09 1135.5

Any suggestions/inputs would help

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-11-24 : 17:26:03

If you create a table of numbers like this:

CREATE TABLE numbers (n int not null primary key)
INSERT numbers VALUES(1)
INSERT numbers VALUES(2)
INSERT numbers VALUES(3)
INSERT numbers VALUES(4)
... repeat as necessary

The following should then work:

SELECT DateAdd(month, DateDiff(month, 0, getdate()), Numbers.n) + StartDayOfMonth -1 PaymentDate, TotalAmt / NoOfPayments PaymentAmount
FROM Numbers INNER JOIN YourTable
WHERE Numbers.N <= NoOfPayments

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:59:57

or use master..spt_values table.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-25 : 17:19:29

quote:

Originally posted by robvolk

If you create a table of numbers like this:

CREATE TABLE numbers (n int not null primary key)
INSERT numbers VALUES(1)
INSERT numbers VALUES(2)
INSERT numbers VALUES(3)
INSERT numbers VALUES(4)
... repeat as necessary

The following should then work:

SELECT DateAdd(month, DateDiff(month, 0, getdate()), Numbers.n) + StartDayOfMonth -1 PaymentDate, TotalAmt / NoOfPayments PaymentAmount
FROM Numbers INNER JOIN YourTable
WHERE Numbers.N <= NoOfPayments

Thanks for the reply but when I run

the below query I get an error message

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

SELECT DateAdd(month, DateDiff(month, 0, getdate()), Nums.id) + 4 -1 PaymentDate, Tot_Amt / Num_Pmt_s PaymentAmount
FROM Nums INNER JOIN [contract]
WHERE Nums.id <= Num_Pmt_s

Any suggestions/inputs would help

Thanks

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-25 : 17:28:00

quote:

Originally posted by robvolk

If you create a table of numbers like this:

CREATE TABLE numbers (n int not null primary key)
INSERT numbers VALUES(1)
INSERT numbers VALUES(2)
INSERT numbers VALUES(3)
INSERT numbers VALUES(4)
... repeat as necessary

The following should then work:

SELECT DateAdd(month, DateDiff(month, 0, getdate()), Numbers.n) + StartDayOfMonth -1 PaymentDate, TotalAmt / NoOfPayments PaymentAmount
FROM Numbers INNER JOIN YourTable
WHERE Numbers.N <= NoOfPayments

when I change the query to following it runs but does not give the desired result

SELECT DateAdd(month, DateDiff(month, 0, getdate()), Nums.id) + start_day_of_month -1 PaymentDate, [contract].Tot_Amt / [contract].Num_Pmt_s PaymentAmount, contrct_num
FROM Nums INNER JOIN [contract] on nums.id = [contract].num_pmt_s
WHERE Nums.id <= [contract].Num_Pmt_s and contrct_num = 200

PymntDate PymntAmt ContrctNum
2008-11-07 00:00:00.000 78.166666 200

SELECT * FROM CONTRACT where contrct_num = 200

num_pmt_s tot_amt contrct_num start_day_of_month
3 234.50 200 4

The result should be
PymntDate PymntAmt ContrctNum
2008-12-04 00:00:00.000 78.166666 200
2009-01-04 00:00:00.000 78.166666 200
2008-02-04 00:00:00.000 78.166666 200

Any suggestions/inputs would help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources