DML for Monthly Schedule

Question

DML for Monthly Schedule

am-244616

SSCarpal Tunnel

Points: 4936
More actions
November 24, 2008 at 1:59 pm

#215672

Guys,
I have to generate monthly payment schedule from the below information
NoofPaymentsTotalAmtContractNumstartdayofmonth
4454214
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
PymntDatePymntAmt
12/4/081135.5
01/4/091135.5
02/4/091135.5
03/4/091135.5
Any suggestions/inputs would help
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

am (11/24/2008)
Guys,
I have to generate monthly payment schedule from the below information
NoofPaymentsTotalAmtContractNumstartdayofmonth
4454214
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
PymntDatePymntAmt
12/4/081135.5
01/4/091135.5
02/4/091135.5
03/4/091135.5
Any suggestions/inputs would help
Thanks

One, some information is missing. You have a start day of month, but how do we know the month and year?

Two, what code have you come up with so far and what error(s) or problems are you having?

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 2

Look up how to use a nums or tally table. Hopefully this will get you started.

Declare @NoofPayments int

Declare @TotalAmt int

Declare @startdayofmonth int

Set @NoofPayments = 4

Set @TotalAmt = 4542

Set @startdayofmonth = 4

Declare @Nums Table(i integer)

Insert Into @Nums Values(1)

Insert Into @Nums Values(2)

Insert Into @Nums Values(3)

Insert Into @Nums Values(4)

Insert Into @Nums Values(5)

Select Cast(@TotalAmt/(@NoofPayments * 1.0) as decimal(18,2)),

Cast(Month(Dateadd(m, i,Getdate())) as varchar(2)) + '/' +

Cast(@startdayofmonth as varchar(2)) + '/' +

Cast(Year(Dateadd(m, i,Getdate())) as varchar(4))

FROM @nums WHERE i <= @NoofPayments

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 3

The start date should always start from next month based on the specific day of the month provided. Regarding my code I am still trying to figure out how do it.

However any suggestions and inputs would help

Thanks