Rent Day Problem - 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

Old Forums

CLOSED - General SQL Server

Rent Day Problem

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-06-30 : 04:42:48

I am doing a Rent App in SQL 2000
Mr Tony Agreement is [Pay per month Frequency] Tenancy will last from 1st June to 31 DEC 2006 Rent = £550pm
Mr Tony Pays rent for 1st of June 2006 - 30th of June 2006
Mr Tonys next payment Date will be 31th of July 2006 ie (ie adding by 1 month Frequency using my initiative )

The Problem
Mr Charlie Agreement is [Pay per month Frequency] Tenancy will last from 3rd June to 31 DEC 2006 rent = £770pm
Mr Charlie Pays rent for 3rd of June 2006 - ???
How do I compute the next Rent payment date, I know I have to add 1 month the problem is that every month has different number
of days eg FEB 28days or 29days (Leap and Unleap year), June =30days etc
How do I make sure that nobody is cheating each other

How do I make the system calculate the accurate next payment date based on their rent frequency, rent frequest might be
1- Pay per month
2- Pay per week
3- pay per quarter etc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 05:06:21

something like this for Pay per month. You should be able to work out per week and per quarter from here

select	rent_payment_date,
	next_rent_payment_date	=
	case 	when rent_payment_date = dateadd(month, datediff(month, 0, rent_payment_date) + 1, -1) then
			dateadd(month, datediff(month, 0, rent_payment_date) + 2, -1)
		else
			dateadd(month, 1, rent_payment_date)
		end
from
(
	select	'2006-02-28' as rent_payment_date union all
	select	'2006-03-31' 	union all
	select	'2006-06-03'	union all
	select  '2006-06-30'	
) r
/* RESULT
rent_payment_date next_rent_payment_date                                 
----------------- -----------------------
2006-02-28        2006-03-31 00:00:00.000
2006-03-31        2006-04-30 00:00:00.000
2006-06-03        2006-07-03 00:00:00.000
2006-06-30        2006-07-31 00:00:00.000
*/

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-06-30 : 05:52:10

I ran it and it returned an error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 05:59:18

[code]select rent_payment_date,
next_rent_payment_date =
case when rent_payment_date = dateadd(month, datediff(month, 0, rent_payment_date) + 1, -1) then
dateadd(month, datediff(month, 0, rent_payment_date) + 2, -1)
else
dateadd(month, 1, rent_payment_date)
end
from
(
select convert(datetime, '20060228') as rent_payment_date union all
select convert(datetime, '20060331') union all
select convert(datetime, '20060603') union all
select convert(datetime, '20060630')
) r
/* RESULT
rent_payment_date next_rent_payment_date
----------------- -----------------------
2006-02-28 2006-03-31 00:00:00.000
2006-03-31 2006-04-30 00:00:00.000
2006-06-03 2006-07-03 00:00:00.000
2006-06-30 2006-07-31 00:00:00.000
*/[/code]

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-06-30 : 09:37:01

Thanks guys, one one last question

How do I make it into a function or stored procedue
such that
I can enter

30TH OF JUNE + rent frequency THEN IT WILL CALCULATE THE NEXT PAYMENT DATE

Function NextPaymentDate(myDate,RentFrequency)

calculation here

return NextPaymentDate

End Function

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-06-30 : 09:54:12

and also for Month Frequency I use month
for week frequency I use week
what if it is a for 2 weeks or a quarter ?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 09:57:17

quote:
Originally posted by OBINNA_EKE

and also for Month Frequency I use month
for week frequency I use week
what if it is a for 2 weeks or a quarter ?
Thanks

for 2 week, you still use week but you add 2 weeks, dateadd(week, 2, rent_payment_date)

for quarter, you still use month, dateadd(month, 3, rent_payment_date)

Correct ?

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-06-30 : 10:11:01

tHaNk yOu vErY mUcH Khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources