SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Rent Calculation Problem (Very Interesting 2 run)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/23/2006 :  09:52:01  Show Profile
I am designing a Rent Payment Plan in SQL 2000, which will be legally binding btw Tenant & LandLord
The calculation must be correct to avoid any party loosing or gaining money
Please copy the below SQL script after the line break **** and start

My Parameters are

RentPlan 'rent','TenancyStart','TenancyEnd','NextPaymentDate','Term','frequecy'

Term means tenant can pay by weekly or monthly :- paramenter is week or month
Frequency means tenant can say I will like to pay every x week or month :- x can be 1,2,3,4 etc

If you run

RentPlan '100','2006/01/01','2006/31/12','2006/01/01','week','1'

All is well
total rent = 5214.2857
No Of day = 365
if you run
RentPlan '100','2006/01/01','2006/31/12','2006/01/01','week','2'

All is well
total rent = 5214.2857
No Of day = 365

BUT IN MONTHLY CALCULATION PROBLEM ARISES

if you run
RentPlan '400','2006/01/01','2006/31/12','2006/01/01','month','1'
it shows you that
No Of dayS = 365
Total Rent = 4685.7140 but it should be 4800 because 12 months x 400 = 4800 (LandLord is short changed by 115)

What can I do to correct this to 4800 and also make the stored procedure to recognise that a tenant can go for e.g
2006/01/01 to 2006/02/15 (which is middle of february) and still does the correct calculation


also
RentPlan '400','2006/01/01','2006/31/12','2006/01/01','month','1'
and
RentPlan '400','2006/01/01','2006/31/12','2006/01/01','month','2'
should give me the same total rent as weekly does

If you see how this can be improved please let me know

Thanks people


'**********************START SCRIPT*************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RentPlan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RentPlan]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE PROCEDURE [dbo].[RentPlan]
@RENT AS VARCHAR(66),
@StartDate as DATETIME,
@EndDate as DATETIME,
@NPD as DATETIME,
@Term varchar(50),
@Frequency varchar(50)
AS

TRUNCATE TABLE X

SET NOCOUNT ON
DECLARE @X INT


DECLARE @rentperday MONEY
DECLARE @MYDAYS INT
DECLARE @LastDate DATETIME

SET @X = 1

IF @TERM='WEEK'
BEGIN
SET @RENTPERDAY = ( Convert(int,@Frequency) * CONVERT(MONEY,@RENT) ) / 7 / Convert(int,@Frequency)
END



IF @TERM='MONTH'
BEGIN
SET @RENTPERDAY = ( Convert(int,@Frequency) * CONVERT(MONEY,@RENT) ) /28 / Convert(int,@Frequency)
END


SELECT @RENTPERDAY AS RentPerDay

WHILE (CONVERT(DATETIME,@NPD) <= CONVERT(DATETIME, @EndDate))
BEGIN
IF (CONVERT(DATETIME,@NPD) <= CONVERT(DATETIME, @EndDate))
BEGIN
SET @NPD = dbo.CalculateNextRentDate(@startdate ,@TERM,@FREQUENCY)

IF (DATEDIFF(day,@Startdate, @Enddate)) > 6 AND @TERM='WEEK'
BEGIN
INSERT INTO X (F,T,RENT) VALUES (@StartDate,@NPD,CONVERT(MONEY,@RENT) * CONVERT(MONEY,@FREQUENCY) )
END
ELSE
BEGIN
IF @TERM='MONTH'
BEGIN
INSERT INTO X (F,T,RENT) VALUES (@StartDate,@NPD, (CONVERT(MONEY,@RENT) * CONVERT(MONEY,@FREQUENCY) ) )
END
ELSE
BEGIN
INSERT INTO X (F,T,RENT) VALUES (@StartDate,@NPD,CONVERT(MONEY,@RENTPERDAY) * CONVERT(MONEY,@FREQUENCY) )
END


END
SET @STARTDATE = @NPD+1
SET @X = @X + 1
END

END

SET @X = (SELECT COUNT(ID) FROM X)
SET @MyDays = 0
SET @LastDate =(SELECT T FROM X WHERE ID =@X)

BEGIN
UPDATE X SET T = @ENDDATE, RENT = @RENTPERDAY*6 WHERE ID = @X
SET @MYDAYS =(SELECT DATEDIFF(day, F,T) FROM X WHERE ID =@X)
UPDATE X SET RENT = ( @RENTPERDAY* @mydays) + @RENTPERDAY WHERE ID = @X
DELETE FROM X WHERE RENT = 0
END



SELECT * FROM aaaa
select SUM(RENT)AS TotalRent, SUM(DAYs) AS TotalDays from aaaa
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[X]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[X]
GO

CREATE TABLE [dbo].[X] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[F] [datetime] NULL ,
[T] [datetime] NULL ,
[Rent] [money] NULL
) ON [PRIMARY]
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateNextRentDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateNextRentDate]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


CREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime,
@Frequency varchar(50) , @Number int)

RETURNS DATETIME

AS
begin


DECLARE @NextPaymentDate Datetime
SET @Number = @Number - 1

IF @Frequency = 'day'
IF @rent_payment_date = dateadd(day, datediff(day, 0+@Number,
@rent_payment_date) + 1, -1)
BEGIN
SET @NextPaymentDate = dateadd(day, datediff(day, 0,
@rent_payment_date) + 2, -1)
END
ELSE
BEGIN
SET @NextPaymentDate = dateadd(day, 1+@Number,
@rent_payment_date)
END

IF @Frequency = 'week'
IF @rent_payment_date = dateadd(week, datediff(week, 0+@Number,
@rent_payment_date) + 1, -1)
BEGIN
SET @NextPaymentDate = dateadd(week, datediff(week, 0,
@rent_payment_date) + 2, -1)-1
END
ELSE
BEGIN
SET @NextPaymentDate = dateadd(week, 1+@Number,
@rent_payment_date)-1
END


IF @Frequency = 'month'
IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number,
@rent_payment_date) + 1, -1)
BEGIN
SET @NextPaymentDate = dateadd(month, datediff(month, 0,
@rent_payment_date) + 2, -1)
END
ELSE
BEGIN
SET @NextPaymentDate = dateadd(month, 1+@Number,
@rent_payment_date)
END

IF @Frequency = 'year'
IF @rent_payment_date = dateadd(year, datediff(year, 0+@Number,
@rent_payment_date) + 1, -1)
BEGIN
SET @NextPaymentDate = dateadd(year, datediff(year, 0,
@rent_payment_date) + 2, -1)
END
ELSE
BEGIN
SET @NextPaymentDate = dateadd(year, 1+@Number,
@rent_payment_date)
END









return @NextPaymentDate
end






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO






if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aaaa]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[aaaa]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.aaaa
AS
SELECT id, F AS [From], T AS [To], Rent, DATEDIFF([day], F, T) + 1 AS Days
FROM dbo.X

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





'**********************END SCRIPT*************************




If it is that easy, everybody will be doing it

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/23/2006 :  11:20:59  Show Profile
Ok I am begging pls help

If it is that easy, everybody will be doing it
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 08/23/2006 :  18:57:22  Show Profile
This won't give you everything your original stored procedure did, but I think it will do most of it. It didn't seem to work if the frequency was greater than 2, but perhaps you can tweak it to get it work (and I only did for months):

CREATE PROCEDURE [dbo].[RentPlan]
@Rent money,
@PeriodRent money,
@StartDate datetime,
@EndDate datetime,
@frequency decimal(3, 1),
@NextStartDate datetime,
@NextEndDate datetime,
@NextMonth datetime,
@Id int

AS

CREATE TABLE #aaa(
[id] int null,
[From] datetime null,
[To] datetime null,
Rent money null,
Days int null)

SELECT @NextStartDate = @StartDate
SELECT @NextMonth = DATEADD(month, 1, @StartDate)

WHILE @NextStartDate < @EndDate
BEGIN
SELECT @NextEndDate = DATEADD(day, ROUND(DATEDIFF(day, @NextStartDate, DATEADD(month, 1, @NextStartDate)) / @frequency, 0), @NextStartDate) -1

IF @NextEndDate >= @NextMonth -1
BEGIN
SET @NextEndDate = @NextMonth -1
SET @NextMonth = DATEADD(month, 1, @NextMonth)
END

SET @PeriodRent = @Rent * (DATEDIFF(day, @NextStartDate, @NextEndDate) +1) / (1.0 * DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @NextStartDate), 0), DATEADD(month, DATEDIFF(month, 0, @NextStartDate) +1, 0)))

INSERT INTO #aaa([id], [From], [To], Rent, Days) VALUES(@Id, @NextStartDate, @NextEndDate, @PeriodRent, DATEDIFF(day, @NextStartDate, @NextEndDate) +1)

SELECT @NextStartDate = @NextEndDate +1

SET @Id = @Id +1
END


SELECT * FROM #aaa

DROP TABLE #aaa

Edited by - nosepicker on 08/23/2006 19:00:58
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/24/2006 :  04:53:03  Show Profile
Thank you nosepicker
The problem is that when you run RentPlan2 400,1,'2006/01/01','2006/14/02','1','2006/01/01','2006/01/01','2006/01/01',1
it does not give you the desired result ie
A tenant that moved in on 1st of January 2006 and will leave on the 14th of February 2006 the rent should be 600 INSTEAD OF 800


If it is that easy, everybody will be doing it
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  06:33:35  Show Profile  Visit SwePeso's Homepage
I hope this is the last we hear about this problem.

select * from dbo.fnRentPlan(100, '20060101', '20061231', '20060101', 'week', 1)
updated version further down...

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 07:08:13
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/24/2006 :  06:47:48  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
aha.. good one.. but i found somthing wrong with the query ..


select * from dbo.fnRentPlan(100, '20060101', '20061231', '20060101', 'week', 1)
PayNo       PaymentDate                                            Rent                  
----------- ------------------------------------------------------ --------------------- 
1           2006-01-01 00:00:00.000                                100.0000
2           2006-01-08 00:00:00.000                                100.0000
3           2006-01-15 00:00:00.000                                100.0000
4           2006-01-22 00:00:00.000                                100.0000
5           2006-01-29 00:00:00.000                                100.0000
6           2006-02-05 00:00:00.000                                100.0000
7           2006-02-12 00:00:00.000                                100.0000
8           2006-02-19 00:00:00.000                                100.0000
9           2006-02-26 00:00:00.000                                100.0000
10          2006-03-05 00:00:00.000                                100.0000
11          2006-03-12 00:00:00.000                                100.0000
12          2006-03-19 00:00:00.000                                100.0000
13          2006-03-26 00:00:00.000                                100.0000
14          2006-04-02 00:00:00.000                                100.0000
15          2006-04-09 00:00:00.000                                100.0000
16          2006-04-16 00:00:00.000                                100.0000
17          2006-04-23 00:00:00.000                                100.0000
18          2006-04-30 00:00:00.000                                100.0000
19          2006-05-07 00:00:00.000                                100.0000
20          2006-05-14 00:00:00.000                                100.0000
21          2006-05-21 00:00:00.000                                100.0000
22          2006-05-28 00:00:00.000                                100.0000
23          2006-06-04 00:00:00.000                                100.0000
24          2006-06-11 00:00:00.000                                100.0000
25          2006-06-18 00:00:00.000                                100.0000
26          2006-06-25 00:00:00.000                                100.0000
27          2006-07-02 00:00:00.000                                100.0000
28          2006-07-09 00:00:00.000                                100.0000
29          2006-07-16 00:00:00.000                                100.0000
30          2006-07-23 00:00:00.000                                100.0000
31          2006-07-30 00:00:00.000                                100.0000
32          2006-08-06 00:00:00.000                                100.0000
33          2006-08-13 00:00:00.000                                100.0000
34          2006-08-20 00:00:00.000                                100.0000
35          2006-08-27 00:00:00.000                                100.0000
36          2006-09-03 00:00:00.000                                100.0000
37          2006-09-10 00:00:00.000                                100.0000
38          2006-09-17 00:00:00.000                                100.0000
39          2006-09-24 00:00:00.000                                100.0000
40          2006-10-01 00:00:00.000                                100.0000
41          2006-10-08 00:00:00.000                                100.0000
42          2006-10-15 00:00:00.000                                100.0000
43          2006-10-22 00:00:00.000                                100.0000
44          2006-10-29 00:00:00.000                                100.0000
45          2006-11-05 00:00:00.000                                100.0000
46          2006-11-12 00:00:00.000                                100.0000
47          2006-11-19 00:00:00.000                                100.0000
48          2006-11-26 00:00:00.000                                100.0000
49          2006-12-03 00:00:00.000                                100.0000
50          2006-12-10 00:00:00.000                                100.0000
51          2006-12-17 00:00:00.000                                100.0000
52          2006-12-24 00:00:00.000                                100.0000
53          2006-12-31 00:00:00.000                                14.2857

select * from dbo.fnRentPlan(100, '20060101', '20061231', '20060101', 'week', 2)

PayNo       PaymentDate                                            Rent                  
----------- ------------------------------------------------------ --------------------- 
1           2006-01-01 00:00:00.000                                100.0000
2           2006-01-15 00:00:00.000                                100.0000
3           2006-01-29 00:00:00.000                                100.0000
4           2006-02-12 00:00:00.000                                100.0000
5           2006-02-26 00:00:00.000                                100.0000
6           2006-03-12 00:00:00.000                                100.0000
7           2006-03-26 00:00:00.000                                100.0000
8           2006-04-09 00:00:00.000                                100.0000
9           2006-04-23 00:00:00.000                                100.0000
10          2006-05-07 00:00:00.000                                100.0000
11          2006-05-21 00:00:00.000                                100.0000
12          2006-06-04 00:00:00.000                                100.0000
13          2006-06-18 00:00:00.000                                100.0000
14          2006-07-02 00:00:00.000                                100.0000
15          2006-07-16 00:00:00.000                                100.0000
16          2006-07-30 00:00:00.000                                100.0000
17          2006-08-13 00:00:00.000                                100.0000
18          2006-08-27 00:00:00.000                                100.0000
19          2006-09-10 00:00:00.000                                100.0000
20          2006-09-24 00:00:00.000                                100.0000
21          2006-10-08 00:00:00.000                                100.0000
22          2006-10-22 00:00:00.000                                100.0000
23          2006-11-05 00:00:00.000                                100.0000
24          2006-11-19 00:00:00.000                                100.0000
25          2006-12-03 00:00:00.000                                100.0000
26          2006-12-17 00:00:00.000                                100.0000
27       2006-12-31 00:00:00.000                                7828.5714 


Also 
select sum(rent) as Sum from dbo.fnRentPlan(100, '20060101', '20061231', '20060101', 'week', 2)

Sum                   
--------------------- 
10428.5714

select sum(rent) as Sum from dbo.fnRentPlan(100, '20060101', '20061231', '20060101', 'week', 2)

Sum                   
--------------------- 
5214.2857

I guess both the sum should be same or its my mistake??


Chirag

Edited by - chiragkhabaria on 08/24/2006 06:50:02
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  07:06:07  Show Profile  Visit SwePeso's Homepage
You blew my Easter Egg. Those money were supposed to the inserted into my secret 11 number Swiss Bank Account

I forgot to multiply the frequency with the rent when inserting
Enhanced version further down...

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 08:42:14
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/24/2006 :  07:15:47  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
quote:

You blew my Easter Egg. Those money were supposed to the inserted into my secret 11 number Swiss Bank Account



you should have promise to share that amount with SQL Team members.. then i wouldnt found any bug.

BTW, this was really a good effort...

Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  07:18:30  Show Profile  Visit SwePeso's Homepage
Thank you, for testing the solution. I feel I should have noticed that myself, but...

I felt sorry for him, that's why I did it.
He has struggled so many weeks with this and I think he has no clue yet where to start himself.

The piecé the resistance is the last update, settling the difference. Also I enhanced the solution to include quarter and year.

BTW, I am sure MVJ will come with a more clever solution soon...


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 07:23:44
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  07:45:50  Show Profile  Visit SwePeso's Homepage
quote:
Originally posted by OBINNA_EKE

A tenant that moved in on 1st of January 2006 and will leave on the 14th of February 2006 the rent should be 600 INSTEAD OF 800
select * from dbo.fnRentPlan (400, '20060101', '20060214', '2006/01/01', 'month', 1)
1	2006-01-01 00:00:00.000	400.0000
2	2006-02-01 00:00:00.000	200.0000

select * from dbo.fnRentPlan (100, '20060101', '20060214', '2006/01/01', 'week', 2)
1	2006-01-01 00:00:00.000	200.0000
2	2006-01-15 00:00:00.000	200.0000
3	2006-01-29 00:00:00.000	200.0000
4	2006-02-12 00:00:00.000	42.8571

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 07:49:13
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/24/2006 :  08:32:55  Show Profile
Thank you nosepicker,chiragkhabaria and a BIG THANK you to Pese You have solved my problem,
Best Solution ever
I think I can relax and enjoy life,
I know u've done a lot is it possible to add
From & To so that the tenant knows the duration, If you can't never mind Atleast I am 100 percent happy

If it is that easy, everybody will be doing it
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  08:41:13  Show Profile  Visit SwePeso's Homepage
quote:
Originally posted by OBINNA_EKE

I know u've done a lot
See super enhanced version further down...

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 09:48:27
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  08:43:16  Show Profile  Visit SwePeso's Homepage
select * from dbo.fnRentPlan (100, '20060101', '20060214', '2006/01/01', 'week', 2)
1	2006-01-01	2006-01-14 	200.0000
2	2006-01-15	2006-01-28 	200.0000
3	2006-01-29	2006-02-11 	200.0000
4	2006-02-12	2006-02-14 	 42.8571

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 08:46:42
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/24/2006 :  09:20:15  Show Profile
PaymentDate Column disappeared Thanks

If it is that easy, everybody will be doing it
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  09:33:24  Show Profile  Visit SwePeso's Homepage
CREATE FUNCTION dbo.fnRentPlan
(
	@Rent MONEY,
	@TenancyStart DATETIME,
	@TenancyEnd DATETIME,
	@NextPaymentDate DATETIME,
	@Term VARCHAR(10),
	@Frequency INT
)
RETURNS	@Payments TABLE (PayNo INT IDENTITY(1, 1), PaymentDate DATETIME, FromDate DATETIME, ToDate DATETIME, Rent SMALLMONEY)
AS

BEGIN
	DECLARE	@Days INT,
		@Periods DECIMAL(38, 12),
		@Payment MONEY,
		@StartNextMonth DATETIME,
		@EndPreviousMonth DATETIME,
		@DaysInStartMonth SMALLINT,
		@DaysInEndMonth SMALLINT

	SELECT	@Days = 1 + DATEDIFF(day, @TenancyStart, @TenancyEnd)

	IF @Term = 'day'
		SELECT	@Periods = @Days

	IF @Term = 'week'
		SELECT	@Periods = DATEDIFF(week, @TenancyStart, @TenancyEnd),
			@Periods = @Periods + (@Days - 7.0 * @Periods) / 7.0

	IF @Term = 'month'
		SELECT	@StartNextMonth = DATEADD(month, 1 + DATEDIFF(month, 0, @TenancyStart), 0),
			@EndPreviousMonth = DATEADD(month, DATEDIFF(month, 0, @TenancyEnd), -1),
			@DaysInStartMonth = DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @TenancyStart), 0), @StartNextMonth),
			@DaysInEndMonth = DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @TenancyEnd), 0), DATEADD(month, 1 + DATEDIFF(month, 0, @TenancyEnd), 0)),
			@Periods = 1 + DATEDIFF(month, @StartNextMonth, @EndPreviousMonth),
	 		@Periods = @Periods + 1.0 * (1 + @DaysInStartMonth - DATEPART(day, @TenancyStart)) / @DaysInStartMonth,
	 		@Periods = @Periods + 1.0 * DATEDIFF(day, @EndPreviousMonth, @TenancyEnd) / @DaysInEndMonth

	IF @Term = 'quarter'
		SELECT	@StartNextMonth = DATEADD(quarter, 1 + DATEDIFF(quarter, 0, @TenancyStart), 0),
			@EndPreviousMonth = DATEADD(quarter, DATEDIFF(quarter, 0, @TenancyEnd), -1),
			@DaysInStartMonth = DATEDIFF(day, DATEADD(quarter, DATEDIFF(quarter, 0, @TenancyStart), 0), @StartNextMonth),
			@DaysInEndMonth = DATEDIFF(day, DATEADD(quarter, DATEDIFF(quarter, 0, @TenancyEnd), 0), DATEADD(quarter, 1 + DATEDIFF(quarter, 0, @TenancyEnd), 0)),
			@Periods = 1 + DATEDIFF(quarter, @StartNextMonth, @EndPreviousMonth),
	 		@Periods = @Periods + 1.0 * (1 + @DaysInStartMonth - DATEPART(day, @TenancyStart)) / @DaysInStartMonth,
	 		@Periods = @Periods + 1.0 * DATEDIFF(day, @EndPreviousMonth, @TenancyEnd) / @DaysInEndMonth

	IF @Term = 'year'
		SELECT	@StartNextMonth = DATEADD(year, 1 + DATEDIFF(year, 0, @TenancyStart), 0),
			@EndPreviousMonth = DATEADD(year, DATEDIFF(year, 0, @TenancyEnd), -1),
			@DaysInStartMonth = DATEDIFF(day, DATEADD(year, DATEDIFF(year, 0, @TenancyStart), 0), @StartNextMonth),
			@DaysInEndMonth = DATEDIFF(day, DATEADD(year, DATEDIFF(year, 0, @TenancyEnd), 0), DATEADD(year, 1 + DATEDIFF(year, 0, @TenancyEnd), 0)),
			@Periods = 1 + DATEDIFF(year, @StartNextMonth, @EndPreviousMonth),
	 		@Periods = @Periods + 1.0 * (1 + @DaysInStartMonth - DATEPART(day, @TenancyStart)) / @DaysInStartMonth,
	 		@Periods = @Periods + 1.0 * DATEDIFF(day, @EndPreviousMonth, @TenancyEnd) / @DaysInEndMonth

	IF @Periods IS NULL
		RETURN

	WHILE @NextPaymentDate <= @TenancyEnd
		BEGIN
			INSERT	@Payments
				(
					PaymentDate,
					FromDate,
					Rent
				)
			SELECT	@NextPaymentDate,
				@TenancyStart,
				@Frequency * @Rent

			SELECT	@NextPaymentDate =	CASE
								WHEN @Term = 'day' THEN	DATEADD(day, @Frequency, @NextPaymentDate)
								WHEN @Term = 'week' THEN DATEADD(week, @Frequency, @NextPaymentDate)
								WHEN @Term = 'month' THEN DATEADD(month, @Frequency, @NextPaymentDate)
								WHEN @Term = 'quarter' THEN DATEADD(quarter, @Frequency, @NextPaymentDate)
								WHEN @Term = 'year' THEN DATEADD(year, @Frequency, @NextPaymentDate)
							END,
				@TenancyStart =	CASE
								WHEN @Term = 'day' THEN	DATEADD(day, @Frequency, @TenancyStart)
								WHEN @Term = 'week' THEN DATEADD(week, @Frequency, @TenancyStart)
								WHEN @Term = 'month' THEN DATEADD(month, @Frequency, @TenancyStart)
								WHEN @Term = 'quarter' THEN DATEADD(quarter, @Frequency, @TenancyStart)
								WHEN @Term = 'year' THEN DATEADD(year, @Frequency, @TenancyStart)
							END
		END

	DECLARE	@PayNo INT,
		@Rents INT
	
	SELECT	@PayNo = MAX(PayNo),
		@Rents = SUM(Rent),
		@Payment = @Periods * @Rent
	FROM	@Payments
	
	UPDATE	@Payments
	SET	Rent = Rent + @Payment - @Rents,
		ToDate = @TenancyEnd
	WHERE	PayNo = @PayNo

	UPDATE	p1
	SET	p1.ToDate  = (SELECT DATEADD(day, -1, p2.FromDate) FROM @Payments p2 WHERE p2.PayNo = p1.PayNo + 1)
	FROM	@Payments p1
	WHERE	p1.PayNo < @PayNo

	DELETE
	FROM	@Payments
	WHERE	ToDate < FromDate

	RETURN
END

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 09:33:42
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/24/2006 :  09:34:42  Show Profile  Visit SwePeso's Homepage
Calling with SELECT * FROM dbo.fnRentPlan (100, '20060102', '20060321', '2005/12/28', 'week', 2) results with following output.
1	2005-12-28	2006-01-02	2006-01-15	200.0000
2	2006-01-11	2006-01-16	2006-01-29	200.0000
3	2006-01-25	2006-01-30	2006-02-12	200.0000
4	2006-02-08	2006-02-13	2006-02-26	200.0000
5	2006-02-22	2006-02-27	2006-03-12	200.0000
6	2006-03-08	2006-03-13	2006-03-21	128.5714
Now, is there anything left to do?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/24/2006 09:35:42
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 08/24/2006 :  09:41:08  Show Profile
Oh gush where are you from ? Planet Genius, Thanks a billion times
I am going to share this code with WWW, Your name will be referenced as well.
Problem = solved

If it is that easy, everybody will be doing it
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000