Thanks Sean that worked. What about the below approach?

;with dates(Month_End) as (select cast('2014-01-01' as datetime)-1 as datetime union all select DATEADD(month, 1, Month_End+1)-1

Forgot to mention that im running SQL 2008 in production and LEAD function is not supported. Is there another way to achieve the same result?

Thanks
Jag

in that case wouldn't it be easier to do the vacancy rate calculation in Tableau/Excel as it will be easier to apply the forumla?

Do we need to use Right outer Join as we are joining properties table with vacant table. Not all active properties are vacant.

Jag

Help then please. 🙂

Hi,

Can you check the vacany rate formula is correctly applied:

DECLARE @vacant TABLE ([Prop Code] INT, [History Ind] INT, [Void Start Date] VARCHAR(23), [Accepted by Allocations] VARCHAR(23), [Let Date] VARCHAR(23), Team...

Please find below the desired output

https://app.box.com/s/eo6045eln5zwjm3jxs282ceefmh3w892

I have joined the calendar table and vacant table but it returns a single row where it should return two rows one for Jan vacant days and Feb Vacant days....

Sorry you are correct it should be 7.

Ok, i got the below Month End Calendar Table. Whats next please

;with dates(Month_End) as

(

select cast('2013-02-01' as datetime)-1 as datetime

union all

select DATEADD(month, 1, Month_End+1)-1

from dates

where Month_End < GETDATE()

)

select * from...