March 21, 2015 at 2:00 am
Hi All,
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will give you an idea of the output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link:
https://app.box.com/s/usl69jfp1rgtwhhr9sfyl58euagv9iqh
Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money
)
INSERT INTO @void (PropCode,VoidStartDate,LetDate, Market_Rent)
VALUES
(1702,'2014/11/19','2014/12/11',239.73)
,(3698,'2014/09/06','2014/10/15',349.04)
select * from @void
Thanks
Jag
March 21, 2015 at 8:56 am
If you want to split your numeric values, simply select them using whatever portion of the value you want for each month. The first thing you're going to want to do is change the data types of the numeric columns from Varchar(10) to an appropriate numeric data type.
With numeric data types, you can do something like this:
SELECT VoidLoss * 0.4 PartForMonth1, VoidLoss * 0.6 PartForMonth2
FROM @voidloss;
March 21, 2015 at 5:33 pm
Hi Ed,
I have updated the explanation and sample table.
Thanks
Jag
March 22, 2015 at 1:05 am
Here is a solution that is based on the data in the OP with some corrections and one multi-month entry added to it. The code is straight forward and somewhat self-explanatory.
😎
USE tempdb;
GO
SET NOCOUNT ON;
Declare @voidloss Table
(
History_IND INT NOT NULL
,PropCode INT NOT NULL
,VoidCategory VARCHAR(10) NOT NULL
,ControlGroup INT NOT NULL
,VoidStartDate date NOT NULL
,VoidMonth VARCHAR(10) NOT NULL
,VoidYear INT NOT NULL
,LetDate date NOT NULL
,LetMonth VARCHAR(10) NOT NULL
,LetYear INT NOT NULL
,VoidDays INT NOT NULL
,MarketRent NUMERIC(14,2) NOT NULL
,VoidLoss NUMERIC(14,2) NOT NULL
)
INSERT INTO @voidloss (HISTORY_IND,PropCode,VoidCategory,ControlGroup,VoidStartDate,VoidMonth,VoidYear,LetDate,LetMonth,LetYear,VoidDays,MarketRent,VoidLoss)
VALUES
(1,3724,'VOI' ,106,'2014/12/01','December' ,2014,'2014/12/08','December' ,2014 ,7 ,420 ,2940 )
,(1,1702,'HB' ,120,'2014/11/19','November' ,2014,'2014/12/11','December' ,2014 ,22 ,239.73 ,5274.06 )
,(2,3730,'ATL' ,108,'2014/11/06','November' ,2014,'2014/11/27','November' ,2014 ,21 ,390 ,8190 )
,(1,3390,'HB' ,100,'2014/10/03','October' ,2014,'2014/10/16','October' ,2014 ,13 ,400 ,5200 )
,(2,3725,'VOI' ,108,'2014/10/03','October' ,2014,'2014/10/24','October' ,2014 ,21 ,385.01 ,8085.21 )
,(2,3698,'ATL' ,106,'2014/09/06','September',2014,'2014/10/15','October' ,2014 ,39 ,250.04 ,13612.56)
,(1,3965,'VOI' ,100,'2015/02/05','February' ,2015,'2015/02/06','February' ,2015 ,1 ,475 ,475 )
,(1,3969,'VOI' ,100,'2015/02/13','February' ,2015,'2015/02/19','February' ,2015 ,6 ,550 ,3300 )
,(3,3791,'ATL' ,108,'2015/01/05','January' ,2015,'2015/01/23','January' ,2015 ,18 ,360 ,6480 )
,(3,9999,'XYZ' ,108,'2014/05/15','May' ,2014,'2015/01/23','January' ,2015 ,253,360 ,91080 )
;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
SELECT
VS.History_IND
,VS.PropCode
,VS.VoidCategory
,VS.ControlGroup
,VS.VoidStartDate
,CAL.CURR_MONTH_NAME AS VoidMonth
,CAL.CURR_YEAR AS VoidYear
,VS.LetDate
,VS.LetMonth
,VS.LetYear
,CALCALC.MM_DAYS AS VoidDays
,VS.MarketRent
,FINANCE_CALC.MONTH_AMOUNT AS VoidLoss
FROM @voidloss VS
CROSS APPLY
(
SELECT
DATEDIFF(MONTH,VS.VoidStartDate,VS.LetDate) + 1 AS MM_DIFF
) AS X
CROSS APPLY
(
SELECT TOP(X.MM_DIFF)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2
) AS NM
CROSS APPLY
(
SELECT
DATEADD(MONTH,NM.N -1,VS.VoidStartDate) AS CURR_MONTH
,DATEADD(MONTH,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),DATEADD(MONTH,NM.N -1,VS.VoidStartDate)),0) AS FIRST_DAY_OF_MONTH
,DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),DATEADD(MONTH,NM.N -1,VS.VoidStartDate))+1,0)) AS LAST_DAY_OF_MONTH
,DATEDIFF(DAY,DATEADD(MONTH,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),DATEADD(MONTH,NM.N -1,VS.VoidStartDate)),0)
,DATEADD(MONTH,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),DATEADD(MONTH,NM.N -1,VS.VoidStartDate))+1,0)) AS DAYS_IN_MONTH
,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),VS.VoidStartDate) AS ZD_START_MONTH
,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),VS.LetDate) AS ZD_END_MONTH
,DATEDIFF(MONTH,CONVERT(DATETIME,0,0),DATEADD(MONTH,NM.N -1,VS.VoidStartDate)) AS ZD_CURRENT_MONTH
,DATENAME(MONTH,DATEADD(MONTH,NM.N -1,VS.VoidStartDate)) AS CURR_MONTH_NAME
,DATEPART(YEAR,DATEADD(MONTH,NM.N -1,VS.VoidStartDate)) AS CURR_YEAR
) AS CAL
CROSS APPLY
(
SELECT
CONVERT(NUMERIC(12,0),
CASE
WHEN CAL.ZD_START_MONTH = CAL.ZD_END_MONTH THEN VS.VoidDays
WHEN CAL.ZD_START_MONTH < CAL.ZD_END_MONTH AND CAL.ZD_START_MONTH = CAL.ZD_CURRENT_MONTH THEN DATEDIFF(DAY,VS.VoidStartDate,CAL.LAST_DAY_OF_MONTH)
WHEN CAL.ZD_START_MONTH < CAL.ZD_CURRENT_MONTH AND CAL.ZD_CURRENT_MONTH < CAL.ZD_END_MONTH THEN CAL.DAYS_IN_MONTH
WHEN CAL.ZD_CURRENT_MONTH = CAL.ZD_END_MONTH THEN DATEDIFF(DAY,CAL.FIRST_DAY_OF_MONTH,VS.LetDate) + 1
END,0) AS MM_DAYS
) AS CALCALC
CROSS APPLY
(
SELECT
CONVERT(NUMERIC(14,2),(VS.MarketRent / 7.0) * CALCALC.MM_DAYS,0) AS MONTH_AMOUNT
) AS FINANCE_CALC
;
The sample data, note that the VoidLoss values are wrong!
History_IND PropCode VoidCategory ControlGroup VoidStartDate VoidMonth VoidYear LetDate LetMonth LetYear VoidDays MarketRent VoidLoss
----------- ----------- ------------ ------------ ------------- ---------- ----------- ---------- ---------- ----------- ----------- ------------ -----------
1 3724 VOI 106 2014-12-01 December 2014 2014-12-08 December 2014 7 420.00 2940.00
1 1702 HB 120 2014-11-19 November 2014 2014-12-11 December 2014 22 239.73 5274.06
2 3730 ATL 108 2014-11-06 November 2014 2014-11-27 November 2014 21 390.00 8190.00
1 3390 HB 100 2014-10-03 October 2014 2014-10-16 October 2014 13 400.00 5200.00
2 3725 VOI 108 2014-10-03 October 2014 2014-10-24 October 2014 21 385.01 8085.21
2 3698 ATL 106 2014-09-06 September 2014 2014-10-15 October 2014 39 250.04 13612.56
1 3965 VOI 100 2015-02-05 February 2015 2015-02-06 February 2015 1 475.00 475.00
1 3969 VOI 100 2015-02-13 February 2015 2015-02-19 February 2015 6 550.00 3300.00
3 3791 ATL 108 2015-01-05 January 2015 2015-01-23 January 2015 18 360.00 6480.00
3 9999 XYZ 108 2014-05-15 May 2014 2015-01-23 January 2015 253 360.00 91080.00
Results
History_IND PropCode VoidCategory ControlGroup VoidStartDate VoidMonth VoidYear LetDate LetMonth LetYear VoidDays MarketRent VoidLoss
----------- ----------- ------------ ------------ ------------- ----------- ----------- ---------- ---------- ----------- ---------- ------------ -----------
1 3724 VOI 106 2014-12-01 December 2014 2014-12-08 December 2014 7 420.00 420.00
1 1702 HB 120 2014-11-19 November 2014 2014-12-11 December 2014 11 239.73 376.72
1 1702 HB 120 2014-11-19 December 2014 2014-12-11 December 2014 11 239.73 376.72
2 3730 ATL 108 2014-11-06 November 2014 2014-11-27 November 2014 21 390.00 1170.00
1 3390 HB 100 2014-10-03 October 2014 2014-10-16 October 2014 13 400.00 742.86
2 3725 VOI 108 2014-10-03 October 2014 2014-10-24 October 2014 21 385.01 1155.03
2 3698 ATL 106 2014-09-06 September 2014 2014-10-15 October 2014 24 250.04 857.28
2 3698 ATL 106 2014-09-06 October 2014 2014-10-15 October 2014 15 250.04 535.80
1 3965 VOI 100 2015-02-05 February 2015 2015-02-06 February 2015 1 475.00 67.86
1 3969 VOI 100 2015-02-13 February 2015 2015-02-19 February 2015 6 550.00 471.43
3 3791 ATL 108 2015-01-05 January 2015 2015-01-23 January 2015 18 360.00 925.71
3 9999 XYZ 108 2014-05-15 May 2014 2015-01-23 January 2015 16 360.00 822.86
3 9999 XYZ 108 2014-05-15 June 2014 2015-01-23 January 2015 30 360.00 1542.86
3 9999 XYZ 108 2014-05-15 July 2014 2015-01-23 January 2015 31 360.00 1594.29
3 9999 XYZ 108 2014-05-15 August 2014 2015-01-23 January 2015 31 360.00 1594.29
3 9999 XYZ 108 2014-05-15 September 2014 2015-01-23 January 2015 30 360.00 1542.86
3 9999 XYZ 108 2014-05-15 October 2014 2015-01-23 January 2015 31 360.00 1594.29
3 9999 XYZ 108 2014-05-15 November 2014 2015-01-23 January 2015 30 360.00 1542.86
3 9999 XYZ 108 2014-05-15 December 2014 2015-01-23 January 2015 31 360.00 1594.29
3 9999 XYZ 108 2014-05-15 January 2015 2015-01-23 January 2015 23 360.00 1182.86
March 22, 2015 at 9:37 am
Is it the days per each month bit that is causing you trouble? If so, you can use this
select DATEDIFF (DAY, VoidStartDate, EOMONTH(VoidStartDate) ) from table1 where Propcode = x
select DATEDIFF (DAY, DATEADD(DAY, 1, EOMONTH(VoidStartDate)) , LetDate) + 1 from table1 where Propcode = x
The first one is the days from the void start to the end of the month, the second is the start of the next month to the letting date, Obviously, that only works for an overlap from one month to the next. You have the formula for the loss calculation , so you can do that part.
I added 1 in the second statement to match your 'result required' answer, you will have to decide the business rule on whether the first day of the let is to be treated as a void day i.e. a new let date of 15th Oct looks like days 1 to 14 of Oct are loss days to me but your business rule might be to include the 15th.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply