| Author |
 Topic  |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
 Posted - 08/23/2006 : 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 - 08/23/2006 : 11:20:59
|
Ok I am begging pls help
If it is that easy, everybody will be doing it |
 |
|
|
nosepicker
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 08/23/2006 : 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
|
Edited by - nosepicker on 08/23/2006 19:00:58 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 08/24/2006 : 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
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 07:08:13 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/24/2006 : 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 |
Edited by - chiragkhabaria on 08/24/2006 06:50:02 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 insertingEnhanced version further down...
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 08/24/2006 08:42:14 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/24/2006 : 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
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 07:23:44 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 07:49:13 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 08/24/2006 : 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
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 09:48:27 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 08:46:42 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 08/24/2006 : 09:20:15
|
PaymentDate Column disappeared Thanks
If it is that easy, everybody will be doing it |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/24/2006 : 09:33:24
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/24/2006 : 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 |
Edited by - SwePeso on 08/24/2006 09:35:42 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 08/24/2006 : 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 |
|
|
| |
Topic |
|
|
|
Rent Calculation Problem (Very Interesting 2 run)
Forum Locked
