Rent Calculation Problem (Very Interesting 2 run)

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 Calculation Problem (Very Interesting 2 run)

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-23 : 09:52:01

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 - 2006-08-23 : 11:20:59

Ok I am begging pls help

If it is that easy, everybody will be doing it

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-23 : 18:57:22

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

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-24 : 04:53:03

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 06:33:35

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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 06:47:48

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 07:06:07

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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 07:15:47

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 07:18:30

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 07:45:50

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

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-24 : 08:32:55

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:41:13

quote:
Originally posted by OBINNA_EKE

I know u've done a lot

See super enhanced version further down...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:43:16

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

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-24 : 09:20:15

PaymentDate Column disappeared Thanks

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 09:33:24

[code]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[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 09:34:42

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

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-08-24 : 09:41:08

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources