Hi All,

I have the tables below and like to join them based on PropCode, Month and Year so that the RentLoss is calculated based on the Market Rent in the particular month.

@MR - stored the Market Rent values

@void - stores details when a property got empty and filled

Query: join @MR and @void such that the Market Rent amount from @MR is selected based on the VoidStartDate in @void

Declare @MR Table
	(	 
		PropCode VARCHAR(10)
		,MRFromDate date
		,MR_Start_Month VARCHAR(10)
		,MR_Start_Year INT
		,MREndDate date
		,MR_End_Month VARCHAR(10)
		,MR_End_Year INT
		,MARKET_RENT Money	
	
	)

INSERT INTO @MR (PropCode, MRFromDate, MR_Start_Month,MR_Start_Year,MREndDate,MR_End_Month,MR_End_Year,MARKET_RENT)
	VALUES
 (3304,'2014/06/15','June',2014,'2014/11/30','November',2014,280)
,(3304,'2014/12/01','December',2014,'2015/04/19','April',2015,290)
,(3304,'2015/04/20','April',2015,'2050/01/01','January',2050,310)
,(3782,'2014/05/17','May',2014,'2014/07/21','July',2014,250)
,(3782,'2014/07/22','July',2014,'2014/11/14','November',2014,275)
,(3782,'2014/12/15','November',2014,'2050/01/01','January',2050,290)

SELECT * from @MR

Declare @void Table
	(	 
		PropCode VARCHAR(10)
		,VoidStartDate date
		,VoidMonth VARCHAR(10)
		,VoidYear VARCHAR(10)
		,LetDate date
		,LetMonth VARCHAR(10)
		,LetYear VARCHAR(10)
		
	)

INSERT INTO @void (PropCode,VoidStartDate,VoidMonth,VoidYear,LetDate,LetMonth,LetYear)
	VALUES
 (3304,'2014/07/15','July',2014,'2014/08/01','August',2014)
,(3782,'2014/05/17','May',2014,'2014/06/12','June',2014)
,(3782,'2014/11/28','November',2014,'2014/12/16','December',2014)

select *, Datediff(Day, VoidStartDate,LetDate) as VoidDays from @void

Result:

PropCode   VoidStartDate    VoidMonth     VoidYear         LetDate        LetMonth     LetYear     VoidDays  Market_Rent    Loss  
3304            2014-07-15         July             2014         2014-08-01      August         2014           17            280            4930
3782            2014-05-17          May            2014         2014-06-12       June           2014           26            250            6500
3782            2014-11-28          November   2014         2014-12-16       December    2014          18             290            5220

• Edited by jaggy99 1 hour 49 minutes ago

Are you not just doing voidstartdate between mrstartdate and mrenddate? Although your example would suggest you want the letdate between start and end.

If you really were trying for months only, convert your month/year to a date and then do a between

In your sample data for @MR you have a row

,(3782,'2014/12/15','November',2014,'2050/01/01','January',2050,290)

I assume you meant that the VoidStartDate should be 2014/11/15 (ie, a date in November, not December).

Also you show the result for Market_Rent for PropCode 3305 VoidStartDate 2014-07-15 as 290, but your data for @MR says the market rate for that date is 280.  I assume you meant to have 280 as the rate for the @void row for that property.

If my assumptions are correct, then the following should work for you

select v.PropCode, v.VoidStartDate, v.VoidMonth, v.VoidYear, v.LetDate, 
    v.LetMonth, v.LetYear, Datediff(Day, v.VoidStartDate, v.LetDate) as VoidDays,
	m.MARKET_RENT, m.MARKET_RENT * Datediff(Day, v.VoidStartDate, v.LetDate) As Loss
from @void v
left join @MR m On v.PropCode = m.PropCode And v.VoidStartDate Between m.MRFromDate And m.MREndDate;

If those assumptions are not correct, could you please give us some more explanation about how you came up with the desired result you showed us.

Also, since you said "Market Rent amount from @MR is selected based on the VoidStartDate in @void", I took the loss rate as being the rate in @MR on VoidStartDate.  So even if the rate changed during the time the property was empty, I continued to use the rate on the start date to calculate the loss.  (Your sample data didn't have any cases of that happening).  If you want to take into account rate changes during the time that would require changes to the above query.  If you do want that, please tell us what you want the result to look like (maybe multiple rows for the same void period - one for each rate in that period, maybe one row with the average loss per day, maybe something else).

Tom

Hi Tom

I have updated the result with the correct value 280. Also thanks for bring up the scenario where the property is empty and market rent is updated and I could use help to revise the query considering this.

Let's take an example

PropCode    MR start date     MR End Date      Market Rent
 1234           01/08/2014          15/12/2014     250
 1234          10/01/2015           16/06/2015      300             

Propcode        Void Start Date        Void End Date
1234              12/12/2014              19/01/2015          

So when the Market Rent changes when the property is empty and the void start date is not in between MR Start Date and MR End Date, the next Market Rent value should be picked up.

Basically Im trying to create a trend line on rent loss movement and the query should pick Market Rent based on the void start date. But yes if the above scenario appear we should take the new market rent value.

Thanks

I basically used the same query Tom provided but switched the voidstartdate with letdate, since that date decides which market rate you need to use for calculation...see if this helps..

made a correction to your input data as ,(3782,'2014/11/15','November',2014,'2050/01/01','January',2050,290)  -- you had it as '2014-12-15' which i think is typo

Declare @MR Table
	(	 
		PropCode VARCHAR(10)
		,MRFromDate date
		,MR_Start_Month VARCHAR(10)
		,MR_Start_Year INT
		,MREndDate date
		,MR_End_Month VARCHAR(10)
		,MR_End_Year INT
		,MARKET_RENT Money	
	
	)

INSERT INTO @MR (PropCode, MRFromDate, MR_Start_Month,MR_Start_Year,MREndDate,MR_End_Month,MR_End_Year,MARKET_RENT)
	VALUES
 (3304,'2014/06/15','June',2014,'2014/11/30','November',2014,280)
,(3304,'2014/12/01','December',2014,'2015/04/19','April',2015,290)
,(3304,'2015/04/20','April',2015,'2050/01/01','January',2050,310)
,(3782,'2014/05/17','May',2014,'2014/07/21','July',2014,250)
,(3782,'2014/07/22','July',2014,'2014/11/14','November',2014,275)
,(3782,'2014/11/15','November',2014,'2050/01/01','January',2050,290)
,(1234,'2014/08/01','August',2014,'2014/12/15','December',2014,250)
 ,(1234,'2015/01/10','October',2015,'2015/06/16','June',2015,300)  

SELECT * from @MR

Declare @void Table
	(	 
		PropCode VARCHAR(10)
		,VoidStartDate date
		,VoidMonth VARCHAR(10)
		,VoidYear VARCHAR(10)
		,LetDate date
		,LetMonth VARCHAR(10)
		,LetYear VARCHAR(10)
		
	)

INSERT INTO @void (PropCode,VoidStartDate,VoidMonth,VoidYear,LetDate,LetMonth,LetYear)
	VALUES
 (3304,'2014/07/15','July',2014,'2014/08/01','August',2014)
,(3782,'2014/05/17','May',2014,'2014/06/12','June',2014)
,(3782,'2014/11/28','November',2014,'2014/12/16','December',2014)
,(1234,'2014/12/12','December',2014,'2015/01/19','Janurary',2015)

select *, Datediff(Day, VoidStartDate,LetDate) as VoidDays from @void

select v.PropCode, v.VoidStartDate, v.VoidMonth, v.VoidYear, v.LetDate, 
    v.LetMonth, v.LetYear, Datediff(Day, v.VoidStartDate, v.LetDate) as VoidDays,
	m.MARKET_RENT, m.MARKET_RENT * Datediff(Day, v.VoidStartDate, v.LetDate) As Loss
from @void v
left join @MR m On v.PropCode = m.PropCode And v.LETDate Between m.MRFromDate And m.MREndDate;