Vacancy Rate

Question

Post reply

Vacancy Rate

jaggy99

Ten Centuries

Points: 1308
More actions
May 10, 2016 at 4:18 am

#313845

Hi,
I'm trying to calculate the property vacancy rate per month which is no of days a property is vacant in a month x the no of vacant properties. Below is the vacant table sample data and wanted to know how to calculate vacant days per property per month.
So if a property was accepted by allocation on 25/01/2016 and let on 05/02/2016;
Jan vacant days 7
Feb vacant days 5
The time from Accepted by Allocations to Let date determines how long the property was vacant.
Declare @vacant TABLE
( [Prop Code] int
, [History Ind] int
, [Void Start Date] varchar(23)
, [Accepted by Allocations] varchar(23)
, [Let Date] varchar(23)
, Team varchar(3)
)
;
INSERT INTO @vacant
([Prop Code], [History Ind], [Void Start Date], [Accepted by Allocations], [Let Date], Team)
VALUES
(1043, 38, '2016-01-18', '2016-01-20', '2016-01-29', 'TAP'),
(1043, 39, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),
(1043, 40, '2016-02-26', '2016-03-01', '2016-03-01', 'TAP'),
(1043, 41, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),
(1045, 29, '2016-01-25', '2016-01-28', '2016-01-29', 'TAP'),
(1045, 30, '2016-02-17', '2016-02-19', '2016-02-24', 'TAP'),
(1045, 31, '2016-03-11', '2016-03-15', '2016-03-18', 'TAP'),
(2448, 36, '2016-01-11', '2016-01-13', '2016-01-19', 'TAP'),
(2448, 37, '2016-03-01', '2016-03-03', '2016-03-04', 'TAP'),
(2485, 35, '2016-02-12', '2016-02-16', '2016-02-19', 'TAP'),
(2485, 33, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),
(2485, 34, '2016-01-20', '2016-01-20', '2016-01-20', 'TAP'),
(2486, 37, '2016-03-10', '2016-03-14', '2016-03-15', 'TAP'),
(2486, 35, '2016-01-14', '2016-01-15', '2016-01-15', 'TAP'),
(2486, 36, '2016-02-15', '2016-02-17', '2016-02-19', 'TAP'),
(2493, 30, '2016-01-12', '2016-01-13', '2016-01-13', 'TAP'),
(2493, 31, '2016-01-27', '2016-01-28', '2016-01-28', 'TAP'),
(2494, 37, '2016-03-08', '2016-03-10', '2016-03-16', 'TAP'),
(2494, 38, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),
(2502, 47, '2016-02-18', '2016-02-22', '2016-02-22', 'TAP'),
(2502, 45, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),
(2502, 46, '2016-01-25', '2016-01-28', '2016-01-28', 'TAP'),
(2510, 38, '2016-02-17', '2016-02-19', '2016-02-23', 'TAP'),
(2510, 39, '2016-02-24', '2016-02-24', '2016-02-24', 'TAP'),
(2510, 36, '2016-01-18', '2016-01-19', '2016-01-19', 'TAP'),
(2510, 37, '2016-02-04', '2016-02-08', '2016-02-11', 'TAP'),
(2513, 39, '2016-01-12', '2016-01-14', '2016-01-15', 'TAP'),
(2513, 40, '2016-03-10', '2016-03-14', '2016-03-16', 'TAP'),
(2716, 4, '2016-01-04', '2016-01-04', '2016-01-07', 'HIP'),
(2727, 35, '2016-02-18', '2016-02-22', '2016-02-24', 'TAP'),
(2727, 34, '2016-02-02', '2016-02-04', '2016-02-10', 'TAP'),
(2756, 43, '2016-01-11', '2016-01-13', '2016-01-15', 'TAP'),
(2756, 44, '2016-03-10', '2016-03-14', '2016-03-17', 'TAP'),
(2841, 43, '2016-03-08', '2016-03-10', '2016-03-10', 'TAP'),
(2841, 40, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),
(2841, 41, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),
(2841, 42, '2016-02-26', '2016-02-29', '2016-02-29', 'TAP'),
(2842, 41, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),
(2842, 42, '2016-03-24', '2016-03-30', '2016-03-31', 'TAP'),
(2842, 40, '2016-01-25', '2016-01-28', '2016-02-03', 'TAP'),
(2843, 42, '2016-01-22', '2016-01-22', '2016-01-22', 'TAP'),
(2843, 43, '2016-01-27', '2016-01-29', '2016-02-01', 'TAP'),
(2843, 44, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),
(2843, 45, '2016-03-11', '2016-03-15', '2016-03-23', 'TAP'),
(2844, 47, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),
(2844, 48, '2016-03-31', '2016-04-04', '2016-04-04', 'TAP'),
(2844, 44, '2016-02-05', '2016-02-08', '2016-02-08', 'TAP'),
(2844, 45, '2016-02-12', '2016-02-16', '2016-02-18', 'TAP'),
(2844, 46, '2016-02-26', '2016-03-01', '2016-03-04', 'TAP'),
(2844, 41, '2016-01-04', '2016-01-06', '2016-01-18', 'TAP'),
(2844, 42, '2016-01-22', '2016-01-25', '2016-01-25', 'TAP'),
(2844, 43, '2016-01-29', '2016-02-02', '2016-02-01', 'TAP'),
(2845, 45, '2016-03-15', '2016-03-17', '2016-03-18', 'TAP'),
(2845, 42, '2016-01-07', '2016-01-11', '2016-01-21', 'TAP'),
(2845, 43, '2016-02-05', '2016-02-09', '2016-02-10', 'TAP'),
(2845, 44, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),
(2997, 35, '2016-02-17', '2016-02-19', '2016-02-22', 'TAP'),
(3009, 33, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),
(3010, 36, '2016-02-03', '2016-02-04', '2016-02-04', 'TAP'),
(3010, 37, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),
(3012, 2, '2016-02-22', '2016-03-04', '2016-03-08', 'HIP'),
(3013, 5, '2016-03-24', '2016-03-24', '2016-03-31', 'HIP'),
(3101, 37, '2016-02-01', '2016-02-03', '2016-02-09', 'TAP'),
(3101, 38, '2016-02-16', '2016-02-18', '2016-02-19', 'TAP'),
(3155, 35, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),
(3216, 3, '2016-03-03', '2016-03-10', '2016-03-18', 'TAP'),
(3216, 4, '2016-03-21', '2016-03-23', '2016-03-24', 'TAP'),
(3252, 40, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),
(3252, 41, '2016-03-14', '2016-03-17', '2016-03-17', 'TAP'),
(3252, 42, '2016-03-23', '2016-03-24', '2016-03-29', 'TAP'),
(3252, 37, '2016-01-05', '2016-01-06', '2016-01-08', 'TAP'),
(3252, 38, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),
(3252, 39, '2016-02-15', '2016-02-17', '2016-02-18', 'TAP'),
(3381, 40, '2016-03-18', '2016-03-21', '2016-03-21', 'TAP'),
(3381, 41, '2016-03-29', '2016-03-31', '2016-04-04', 'TAP'),
(3381, 38, '2016-02-05', '2016-02-09', '2016-02-09', 'TAP'),
(3381, 39, '2016-02-17', '2016-02-18', '2016-02-18', 'TAP'),
(3383, 40, '2016-03-21', '2016-03-23', '2016-04-08', 'TAP'),
(3384, 35, '2016-01-11', '2016-01-11', '2016-01-11', 'TAP'),
(3384, 36, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),
(3384, 37, '2016-02-08', '2016-02-10', '2016-02-18', 'TAP'),
(3384, 38, '2016-02-19', '2016-02-23', '2016-02-23', 'TAP'),
(3385, 36, '2016-01-18', '2016-01-20', '2016-01-22', 'TAP'),
(3385, 37, '2016-02-17', '2016-02-19', '2016-02-19', 'TAP'),
(3394, 3, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),
(3407, 39, '2016-02-22', '2016-02-24', '2016-02-26', 'TAP'),
(3411, 3, '2016-01-27', '2016-01-28', '2016-02-17', 'HIP'),
(3416, 33, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),
(3416, 34, '2016-02-11', '2016-02-15', '2016-02-15', 'TAP'),
(3418, 33, '2016-01-06', '2016-01-08', '2016-01-11', 'TAP'),
(3418, 34, '2016-03-04', '2016-03-08', '2016-03-09', 'TAP'),
(3421, 36, '2016-01-29', '2016-02-02', '2016-02-04', 'TAP'),
(3421, 37, '2016-02-25', '2016-02-29', '2016-03-01', 'TAP'),
(3422, 5, '2016-02-05', '2016-02-05', '2016-02-05', 'HIP'),
(3431, 5, '2016-01-04', '2016-01-07', '2016-01-08', 'TAP'),
(3440, 3, '2016-02-08', '2016-02-10', '2016-02-10', 'TAP'),
(3441, 37, '2016-01-08', '2016-01-12', '2016-01-20', 'TAP'),
(3441, 38, '2016-02-03', '2016-02-04', '2016-02-05', 'TAP'),
(3443, 2, '2016-01-20', '2016-01-22', '2016-02-05', 'HIP'),
(3457, 7, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),
(3457, 6, '2016-01-25', '2016-01-27', '2016-02-09', 'TAP'),
(3459, 15, '2016-01-22', '2016-01-26', '2016-02-04', 'TAP'),
(3475, 19, '2016-01-20', '2016-01-25', '2016-02-03', 'TAP'),
(3475, 20, '2016-02-03', '2016-02-03', '2016-02-16', 'TAP'),
(3475, 21, '2016-03-14', '2016-03-16', '2016-03-16', 'TAP'),
(3476, 4, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),
(3479, 6, '2016-01-25', '2016-01-28', '2016-02-02', 'TAP'),
(3479, 7, '2016-02-14', '2016-02-16', '2016-02-17', 'TAP'),
(3479, 8, '2016-03-10', '2016-03-14', '2016-03-24', 'TAP'),
(3485, 13, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),
(3485, 14, '2016-02-11', '2016-02-15', '2016-02-23', 'TAP'),
(3486, 7, '2016-03-07', '2016-03-10', '2016-03-10', 'TAP'),
(3490, 20, '2016-03-17', '2016-03-18', '2016-03-18', 'TAP'),
(3490, 18, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),
(3490, 19, '2016-02-05', '2016-02-08', '2016-02-15', 'TAP'),
(3497, 3, '2016-02-08', '2016-02-08', '2016-02-08', 'HIP'),
(3498, 4, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),
(3498, 5, '2016-02-16', '2016-02-18', '2016-02-18', 'TAP'),
(3498, 6, '2016-03-01', '2016-03-02', '2016-03-02', 'TAP'),
(3499, 4, '2016-01-28', '2016-02-02', '2016-02-02', 'HIP'),
(3500, 6, '2016-01-12', '2016-01-12', '2016-01-13', 'TAP'),
(3500, 7, '2016-02-04', '2016-02-08', '2016-02-09', 'TAP'),
(3500, 8, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),
(3501, 3, '2016-01-29', '2016-02-01', '2016-02-12', 'HIP'),
(3502, 3, '2016-01-19', '2016-01-25', '2016-01-25', 'HIP'),
(3567, 8, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),
(3567, 9, '2016-03-03', '2016-03-04', '2016-03-04', 'TAP'),
(3567, 10, '2016-03-18', '2016-03-18', '2016-03-18', 'TAP'),
(3567, 7, '2016-01-15', '2016-01-18', '2016-01-18', 'TAP'),
(3607, 43, '2016-03-31', '2016-04-04', '2016-04-06', 'TAP'),
(3607, 41, '2016-02-02', '2016-02-04', '2016-02-04', 'TAP'),
(3607, 42, '2016-03-16', '2016-03-18', '2016-03-24', 'TAP'),
(3678, 13, '2016-01-13', '2016-01-18', '2016-02-03', 'TAP'),
(3678, 14, '2016-03-16', '2016-03-18', '2016-03-18', 'TAP'),
(3679, 7, '2016-01-29', '2016-02-02', '2016-02-02', 'TAP'),
(3686, 5, '2016-01-11', '2016-01-11', '2016-01-12', 'HIP'),
(3687, 19, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),
(3731, 35, '2016-01-14', '2016-01-18', '2016-01-20', 'TAP'),
(3731, 36, '2016-01-27', '2016-01-29', '2016-01-29', 'TAP'),
(3731, 37, '2016-02-10', '2016-02-12', '2016-02-19', 'TAP'),
(3731, 38, '2016-03-08', '2016-03-10', '2016-03-14', 'TAP'),
(3743, 5, '2016-02-01', '2016-02-04', '2016-02-17', 'TAP'),
(3752, 4, '2016-01-20', '2016-02-01', '2016-02-03', 'HIP'),
(3759, 4, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),
(3761, 4, '2016-02-16', '2016-02-29', '2016-03-02', 'TAP'),
(3761, 5, '2016-03-29', '2016-03-31', '2016-04-05', 'TAP'),
(3809, 4, '2016-01-13', '2016-01-18', '2016-01-29', 'HIP'),
(3836, 3, '2016-01-28', '2016-02-05', '2016-02-08', 'HIP'),
(3908, 5, '2016-02-08', '2016-02-16', '2016-02-23', 'HIP'),
(3915, 5, '2016-03-29', '2016-03-29', '2016-03-31', 'HIP'),
(3983, 2, '2016-01-05', '2016-01-05', '2016-01-19', 'HIP'),
(3984, 2, '2016-03-29', '2016-03-30', '2016-04-06', 'HIP'),
(3986, 5, '2016-03-15', '2016-03-16', '2016-03-18', 'TAP'),
(3986, 6, '2016-03-29', '2016-03-29', '2016-04-12', 'TAP'),
(4051, 1, '2016-02-29', '2016-03-01', '2016-03-01', 'HIP')
;
select *, DATEDIFF(day, [Accepted by Allocations], [Let Date]) As [Vacant Days]
from @vacant

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 1

Could you create a calendar table (lots of examples online), then use that the get the 'days vacant' number by month?

jaggy99 Ten Centuries Points: 1308 More actions · Answer 2

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 dates

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 3

I would have probably made the calendar table to include date, year, month, day of month, days until month end etc. Then you could join on date to get the information you need (using 'days until month end' as appropriate). It would also make the table useful for future tasks 🙂

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

jaggy99 (5/10/2016)
Hi,
I'm trying to calculate the property vacancy rate per month which is no of days a property is vacant in a month x the no of vacant properties. Below is the vacant table sample data and wanted to know how to calculate vacant days per property per month.
So if a property was accepted by allocation on 25/01/2016 and let on 05/02/2016;
Jan vacant days 6
Feb vacant days 5

Why does January only have 6 days? You have days of 25, 26,27,28,29,30,31. That is 7 days. But on the other end you have 5 days for 1,2,3,4,5. Do you just always want to add 1 to the AcceptedByAllocations value? What do you expect as output from this sample data? Are you expecting a column for each month of the year and that column has the number of days vacant for that month? You did a great job posting a table and sample data. Once we understand the business rules and the desired output we can make this happen fairly easily.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jaggy99 Ten Centuries Points: 1308 More actions · Answer 5

jaggy99

Ten Centuries

Points: 1308

May 10, 2016 at 7:30 am

#1877777

Sorry you are correct it should be 7.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

jaggy99 (5/10/2016)
Sorry you are correct it should be 7.

That answers my first question.

The bigger question though is what do you expect for output from your sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jaggy99 Ten Centuries Points: 1308 More actions · Answer 7

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. Not sure how to embed the picture and i have uploaded it instead.

Declare @vacant TABLE

( [Prop Code] int

, [History Ind] int

, [Void Start Date] varchar(23)

, [Accepted by Allocations] varchar(23)

, [Let Date] varchar(23)

, Team varchar(3)

)

INSERT INTO @vacant

([Prop Code], [History Ind], [Void Start Date], [Accepted by Allocations], [Let Date], Team)

VALUES

(1043, 38, '2016-01-18', '2016-01-20', '2016-01-29', 'TAP'),

(1043, 39, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),

(1043, 40, '2016-02-26', '2016-03-01', '2016-03-01', 'TAP'),

(1043, 41, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),

(1045, 29, '2016-01-25', '2016-01-28', '2016-01-29', 'TAP'),

(1045, 30, '2016-02-17', '2016-02-19', '2016-02-24', 'TAP'),

(1045, 31, '2016-03-11', '2016-03-15', '2016-03-18', 'TAP'),

(2448, 36, '2016-01-11', '2016-01-13', '2016-01-19', 'TAP'),

(2448, 37, '2016-03-01', '2016-03-03', '2016-03-04', 'TAP'),

(2485, 35, '2016-02-12', '2016-02-16', '2016-02-19', 'TAP'),

(2485, 33, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),

(2485, 34, '2016-01-20', '2016-01-20', '2016-01-20', 'TAP'),

(2486, 37, '2016-03-10', '2016-03-14', '2016-03-15', 'TAP'),

(2486, 35, '2016-01-14', '2016-01-15', '2016-01-15', 'TAP'),

(2486, 36, '2016-02-15', '2016-02-17', '2016-02-19', 'TAP'),

(2493, 30, '2016-01-12', '2016-01-13', '2016-01-13', 'TAP'),

(2493, 31, '2016-01-27', '2016-01-28', '2016-01-28', 'TAP'),

(2494, 37, '2016-03-08', '2016-03-10', '2016-03-16', 'TAP'),

(2494, 38, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),

(2502, 47, '2016-02-18', '2016-02-22', '2016-02-22', 'TAP'),

(2502, 45, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),

(2502, 46, '2016-01-25', '2016-01-28', '2016-01-28', 'TAP'),

(2510, 38, '2016-02-17', '2016-02-19', '2016-02-23', 'TAP'),

(2510, 39, '2016-02-24', '2016-02-24', '2016-02-24', 'TAP'),

(2510, 36, '2016-01-18', '2016-01-19', '2016-01-19', 'TAP'),

(2510, 37, '2016-02-04', '2016-02-08', '2016-02-11', 'TAP'),

(2513, 39, '2016-01-12', '2016-01-14', '2016-01-15', 'TAP'),

(2513, 40, '2016-03-10', '2016-03-14', '2016-03-16', 'TAP'),

(2716, 4, '2016-01-04', '2016-01-04', '2016-01-07', 'HIP'),

(2727, 35, '2016-02-18', '2016-02-22', '2016-02-24', 'TAP'),

(2727, 34, '2016-02-02', '2016-02-04', '2016-02-10', 'TAP'),

(2756, 43, '2016-01-11', '2016-01-13', '2016-01-15', 'TAP'),

(2756, 44, '2016-03-10', '2016-03-14', '2016-03-17', 'TAP'),

(2841, 43, '2016-03-08', '2016-03-10', '2016-03-10', 'TAP'),

(2841, 40, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),

(2841, 41, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),

(2841, 42, '2016-02-26', '2016-02-29', '2016-02-29', 'TAP'),

(2842, 41, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),

(2842, 42, '2016-03-24', '2016-03-30', '2016-03-31', 'TAP'),

(2842, 40, '2016-01-25', '2016-01-28', '2016-02-03', 'TAP'),

(2843, 42, '2016-01-22', '2016-01-22', '2016-01-22', 'TAP'),

(2843, 43, '2016-01-27', '2016-01-29', '2016-02-01', 'TAP'),

(2843, 44, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),

(2843, 45, '2016-03-11', '2016-03-15', '2016-03-23', 'TAP'),

(2844, 47, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),

(2844, 48, '2016-03-31', '2016-04-04', '2016-04-04', 'TAP'),

(2844, 44, '2016-02-05', '2016-02-08', '2016-02-08', 'TAP'),

(2844, 45, '2016-02-12', '2016-02-16', '2016-02-18', 'TAP'),

(2844, 46, '2016-02-26', '2016-03-01', '2016-03-04', 'TAP'),

(2844, 41, '2016-01-04', '2016-01-06', '2016-01-18', 'TAP'),

(2844, 42, '2016-01-22', '2016-01-25', '2016-01-25', 'TAP'),

(2844, 43, '2016-01-29', '2016-02-02', '2016-02-01', 'TAP'),

(2845, 45, '2016-03-15', '2016-03-17', '2016-03-18', 'TAP'),

(2845, 42, '2016-01-07', '2016-01-11', '2016-01-21', 'TAP'),

(2845, 43, '2016-02-05', '2016-02-09', '2016-02-10', 'TAP'),

(2845, 44, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),

(2997, 35, '2016-02-17', '2016-02-19', '2016-02-22', 'TAP'),

(3009, 33, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),

(3010, 36, '2016-02-03', '2016-02-04', '2016-02-04', 'TAP'),

(3010, 37, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),

(3012, 2, '2016-02-22', '2016-03-04', '2016-03-08', 'HIP'),

(3013, 5, '2016-03-24', '2016-03-24', '2016-03-31', 'HIP'),

(3101, 37, '2016-02-01', '2016-02-03', '2016-02-09', 'TAP'),

(3101, 38, '2016-02-16', '2016-02-18', '2016-02-19', 'TAP'),

(3155, 35, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),

(3216, 3, '2016-03-03', '2016-03-10', '2016-03-18', 'TAP'),

(3216, 4, '2016-03-21', '2016-03-23', '2016-03-24', 'TAP'),

(3252, 40, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),

(3252, 41, '2016-03-14', '2016-03-17', '2016-03-17', 'TAP'),

(3252, 42, '2016-03-23', '2016-03-24', '2016-03-29', 'TAP'),

(3252, 37, '2016-01-05', '2016-01-06', '2016-01-08', 'TAP'),

(3252, 38, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),

(3252, 39, '2016-02-15', '2016-02-17', '2016-02-18', 'TAP'),

(3381, 40, '2016-03-18', '2016-03-21', '2016-03-21', 'TAP'),

(3381, 41, '2016-03-29', '2016-03-31', '2016-04-04', 'TAP'),

(3381, 38, '2016-02-05', '2016-02-09', '2016-02-09', 'TAP'),

(3381, 39, '2016-02-17', '2016-02-18', '2016-02-18', 'TAP'),

(3383, 40, '2016-03-21', '2016-03-23', '2016-04-08', 'TAP'),

(3384, 35, '2016-01-11', '2016-01-11', '2016-01-11', 'TAP'),

(3384, 36, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),

(3384, 37, '2016-02-08', '2016-02-10', '2016-02-18', 'TAP'),

(3384, 38, '2016-02-19', '2016-02-23', '2016-02-23', 'TAP'),

(3385, 36, '2016-01-18', '2016-01-20', '2016-01-22', 'TAP'),

(3385, 37, '2016-02-17', '2016-02-19', '2016-02-19', 'TAP'),

(3394, 3, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),

(3407, 39, '2016-02-22', '2016-02-24', '2016-02-26', 'TAP'),

(3411, 3, '2016-01-27', '2016-01-28', '2016-02-17', 'HIP'),

(3416, 33, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),

(3416, 34, '2016-02-11', '2016-02-15', '2016-02-15', 'TAP'),

(3418, 33, '2016-01-06', '2016-01-08', '2016-01-11', 'TAP'),

(3418, 34, '2016-03-04', '2016-03-08', '2016-03-09', 'TAP'),

(3421, 36, '2016-01-29', '2016-02-02', '2016-02-04', 'TAP'),

(3421, 37, '2016-02-25', '2016-02-29', '2016-03-01', 'TAP'),

(3422, 5, '2016-02-05', '2016-02-05', '2016-02-05', 'HIP'),

(3431, 5, '2016-01-04', '2016-01-07', '2016-01-08', 'TAP'),

(3440, 3, '2016-02-08', '2016-02-10', '2016-02-10', 'TAP'),

(3441, 37, '2016-01-08', '2016-01-12', '2016-01-20', 'TAP'),

(3441, 38, '2016-02-03', '2016-02-04', '2016-02-05', 'TAP'),

(3443, 2, '2016-01-20', '2016-01-22', '2016-02-05', 'HIP'),

(3457, 7, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),

(3457, 6, '2016-01-25', '2016-01-27', '2016-02-09', 'TAP'),

(3459, 15, '2016-01-22', '2016-01-26', '2016-02-04', 'TAP'),

(3475, 19, '2016-01-20', '2016-01-25', '2016-02-03', 'TAP'),

(3475, 20, '2016-02-03', '2016-02-03', '2016-02-16', 'TAP'),

(3475, 21, '2016-03-14', '2016-03-16', '2016-03-16', 'TAP'),

(3476, 4, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),

(3479, 6, '2016-01-25', '2016-01-28', '2016-02-02', 'TAP'),

(3479, 7, '2016-02-14', '2016-02-16', '2016-02-17', 'TAP'),

(3479, 8, '2016-03-10', '2016-03-14', '2016-03-24', 'TAP'),

(3485, 13, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),

(3485, 14, '2016-02-11', '2016-02-15', '2016-02-23', 'TAP'),

(3486, 7, '2016-03-07', '2016-03-10', '2016-03-10', 'TAP'),

(3490, 20, '2016-03-17', '2016-03-18', '2016-03-18', 'TAP'),

(3490, 18, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),

(3490, 19, '2016-02-05', '2016-02-08', '2016-02-15', 'TAP'),

(3497, 3, '2016-02-08', '2016-02-08', '2016-02-08', 'HIP'),

(3498, 4, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),

(3498, 5, '2016-02-16', '2016-02-18', '2016-02-18', 'TAP'),

(3498, 6, '2016-03-01', '2016-03-02', '2016-03-02', 'TAP'),

(3499, 4, '2016-01-28', '2016-02-02', '2016-02-02', 'HIP'),

(3500, 6, '2016-01-12', '2016-01-12', '2016-01-13', 'TAP'),

(3500, 7, '2016-02-04', '2016-02-08', '2016-02-09', 'TAP'),

(3500, 8, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),

(3501, 3, '2016-01-29', '2016-02-01', '2016-02-12', 'HIP'),

(3502, 3, '2016-01-19', '2016-01-25', '2016-01-25', 'HIP'),

(3567, 8, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),

(3567, 9, '2016-03-03', '2016-03-04', '2016-03-04', 'TAP'),

(3567, 10, '2016-03-18', '2016-03-18', '2016-03-18', 'TAP'),

(3567, 7, '2016-01-15', '2016-01-18', '2016-01-18', 'TAP'),

(3607, 43, '2016-03-31', '2016-04-04', '2016-04-06', 'TAP'),

(3607, 41, '2016-02-02', '2016-02-04', '2016-02-04', 'TAP'),

(3607, 42, '2016-03-16', '2016-03-18', '2016-03-24', 'TAP'),

(3678, 13, '2016-01-13', '2016-01-18', '2016-02-03', 'TAP'),

(3678, 14, '2016-03-16', '2016-03-18', '2016-03-18', 'TAP'),

(3679, 7, '2016-01-29', '2016-02-02', '2016-02-02', 'TAP'),

(3686, 5, '2016-01-11', '2016-01-11', '2016-01-12', 'HIP'),

(3687, 19, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),

(3731, 35, '2016-01-14', '2016-01-18', '2016-01-20', 'TAP'),

(3731, 36, '2016-01-27', '2016-01-29', '2016-01-29', 'TAP'),

(3731, 37, '2016-02-10', '2016-02-12', '2016-02-19', 'TAP'),

(3731, 38, '2016-03-08', '2016-03-10', '2016-03-14', 'TAP'),

(3743, 5, '2016-02-01', '2016-02-04', '2016-02-17', 'TAP'),

(3752, 4, '2016-01-20', '2016-02-01', '2016-02-03', 'HIP'),

(3759, 4, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),

(3761, 4, '2016-02-16', '2016-02-29', '2016-03-02', 'TAP'),

(3761, 5, '2016-03-29', '2016-03-31', '2016-04-05', 'TAP'),

(3809, 4, '2016-01-13', '2016-01-18', '2016-01-29', 'HIP'),

(3836, 3, '2016-01-28', '2016-02-05', '2016-02-08', 'HIP'),

(3908, 5, '2016-02-08', '2016-02-16', '2016-02-23', 'HIP'),

(3915, 5, '2016-03-29', '2016-03-29', '2016-03-31', 'HIP'),

(3983, 2, '2016-01-05', '2016-01-05', '2016-01-19', 'HIP'),

(3984, 2, '2016-03-29', '2016-03-30', '2016-04-06', 'HIP'),

(3986, 5, '2016-03-15', '2016-03-16', '2016-03-18', 'TAP'),

(3986, 6, '2016-03-29', '2016-03-29', '2016-04-12', 'TAP'),

(4051, 1, '2016-02-29', '2016-03-01', '2016-03-01', 'HIP')

;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 Month_End,

[Prop Code], [History Ind],[Accepted by Allocations], [Let Date], Team

FROM @vacant v join dates d ON d.Month_End between [Accepted by Allocations] and isnull([Let Date], GETDATE())

Where Team = 'TAP' and [Prop Code] = '3459'

order by MOnth_End

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

jaggy99 (5/10/2016)
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. Not sure how to embed the picture and i have uploaded it instead.
Declare @vacant TABLE
( [Prop Code] int
, [History Ind] int
, [Void Start Date] varchar(23)
, [Accepted by Allocations] varchar(23)
, [Let Date] varchar(23)
, Team varchar(3)
)
INSERT INTO @vacant
([Prop Code], [History Ind], [Void Start Date], [Accepted by Allocations], [Let Date], Team)
VALUES
(1043, 38, '2016-01-18', '2016-01-20', '2016-01-29', 'TAP'),
(1043, 39, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),
(1043, 40, '2016-02-26', '2016-03-01', '2016-03-01', 'TAP'),
(1043, 41, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),
(1045, 29, '2016-01-25', '2016-01-28', '2016-01-29', 'TAP'),
(1045, 30, '2016-02-17', '2016-02-19', '2016-02-24', 'TAP'),
(1045, 31, '2016-03-11', '2016-03-15', '2016-03-18', 'TAP'),
(2448, 36, '2016-01-11', '2016-01-13', '2016-01-19', 'TAP'),
(2448, 37, '2016-03-01', '2016-03-03', '2016-03-04', 'TAP'),
(2485, 35, '2016-02-12', '2016-02-16', '2016-02-19', 'TAP'),
(2485, 33, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),
(2485, 34, '2016-01-20', '2016-01-20', '2016-01-20', 'TAP'),
(2486, 37, '2016-03-10', '2016-03-14', '2016-03-15', 'TAP'),
(2486, 35, '2016-01-14', '2016-01-15', '2016-01-15', 'TAP'),
(2486, 36, '2016-02-15', '2016-02-17', '2016-02-19', 'TAP'),
(2493, 30, '2016-01-12', '2016-01-13', '2016-01-13', 'TAP'),
(2493, 31, '2016-01-27', '2016-01-28', '2016-01-28', 'TAP'),
(2494, 37, '2016-03-08', '2016-03-10', '2016-03-16', 'TAP'),
(2494, 38, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),
(2502, 47, '2016-02-18', '2016-02-22', '2016-02-22', 'TAP'),
(2502, 45, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),
(2502, 46, '2016-01-25', '2016-01-28', '2016-01-28', 'TAP'),
(2510, 38, '2016-02-17', '2016-02-19', '2016-02-23', 'TAP'),
(2510, 39, '2016-02-24', '2016-02-24', '2016-02-24', 'TAP'),
(2510, 36, '2016-01-18', '2016-01-19', '2016-01-19', 'TAP'),
(2510, 37, '2016-02-04', '2016-02-08', '2016-02-11', 'TAP'),
(2513, 39, '2016-01-12', '2016-01-14', '2016-01-15', 'TAP'),
(2513, 40, '2016-03-10', '2016-03-14', '2016-03-16', 'TAP'),
(2716, 4, '2016-01-04', '2016-01-04', '2016-01-07', 'HIP'),
(2727, 35, '2016-02-18', '2016-02-22', '2016-02-24', 'TAP'),
(2727, 34, '2016-02-02', '2016-02-04', '2016-02-10', 'TAP'),
(2756, 43, '2016-01-11', '2016-01-13', '2016-01-15', 'TAP'),
(2756, 44, '2016-03-10', '2016-03-14', '2016-03-17', 'TAP'),
(2841, 43, '2016-03-08', '2016-03-10', '2016-03-10', 'TAP'),
(2841, 40, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),
(2841, 41, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),
(2841, 42, '2016-02-26', '2016-02-29', '2016-02-29', 'TAP'),
(2842, 41, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),
(2842, 42, '2016-03-24', '2016-03-30', '2016-03-31', 'TAP'),
(2842, 40, '2016-01-25', '2016-01-28', '2016-02-03', 'TAP'),
(2843, 42, '2016-01-22', '2016-01-22', '2016-01-22', 'TAP'),
(2843, 43, '2016-01-27', '2016-01-29', '2016-02-01', 'TAP'),
(2843, 44, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),
(2843, 45, '2016-03-11', '2016-03-15', '2016-03-23', 'TAP'),
(2844, 47, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),
(2844, 48, '2016-03-31', '2016-04-04', '2016-04-04', 'TAP'),
(2844, 44, '2016-02-05', '2016-02-08', '2016-02-08', 'TAP'),
(2844, 45, '2016-02-12', '2016-02-16', '2016-02-18', 'TAP'),
(2844, 46, '2016-02-26', '2016-03-01', '2016-03-04', 'TAP'),
(2844, 41, '2016-01-04', '2016-01-06', '2016-01-18', 'TAP'),
(2844, 42, '2016-01-22', '2016-01-25', '2016-01-25', 'TAP'),
(2844, 43, '2016-01-29', '2016-02-02', '2016-02-01', 'TAP'),
(2845, 45, '2016-03-15', '2016-03-17', '2016-03-18', 'TAP'),
(2845, 42, '2016-01-07', '2016-01-11', '2016-01-21', 'TAP'),
(2845, 43, '2016-02-05', '2016-02-09', '2016-02-10', 'TAP'),
(2845, 44, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),
(2997, 35, '2016-02-17', '2016-02-19', '2016-02-22', 'TAP'),
(3009, 33, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),
(3010, 36, '2016-02-03', '2016-02-04', '2016-02-04', 'TAP'),
(3010, 37, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),
(3012, 2, '2016-02-22', '2016-03-04', '2016-03-08', 'HIP'),
(3013, 5, '2016-03-24', '2016-03-24', '2016-03-31', 'HIP'),
(3101, 37, '2016-02-01', '2016-02-03', '2016-02-09', 'TAP'),
(3101, 38, '2016-02-16', '2016-02-18', '2016-02-19', 'TAP'),
(3155, 35, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),
(3216, 3, '2016-03-03', '2016-03-10', '2016-03-18', 'TAP'),
(3216, 4, '2016-03-21', '2016-03-23', '2016-03-24', 'TAP'),
(3252, 40, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),
(3252, 41, '2016-03-14', '2016-03-17', '2016-03-17', 'TAP'),
(3252, 42, '2016-03-23', '2016-03-24', '2016-03-29', 'TAP'),
(3252, 37, '2016-01-05', '2016-01-06', '2016-01-08', 'TAP'),
(3252, 38, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),
(3252, 39, '2016-02-15', '2016-02-17', '2016-02-18', 'TAP'),
(3381, 40, '2016-03-18', '2016-03-21', '2016-03-21', 'TAP'),
(3381, 41, '2016-03-29', '2016-03-31', '2016-04-04', 'TAP'),
(3381, 38, '2016-02-05', '2016-02-09', '2016-02-09', 'TAP'),
(3381, 39, '2016-02-17', '2016-02-18', '2016-02-18', 'TAP'),
(3383, 40, '2016-03-21', '2016-03-23', '2016-04-08', 'TAP'),
(3384, 35, '2016-01-11', '2016-01-11', '2016-01-11', 'TAP'),
(3384, 36, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),
(3384, 37, '2016-02-08', '2016-02-10', '2016-02-18', 'TAP'),
(3384, 38, '2016-02-19', '2016-02-23', '2016-02-23', 'TAP'),
(3385, 36, '2016-01-18', '2016-01-20', '2016-01-22', 'TAP'),
(3385, 37, '2016-02-17', '2016-02-19', '2016-02-19', 'TAP'),
(3394, 3, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),
(3407, 39, '2016-02-22', '2016-02-24', '2016-02-26', 'TAP'),
(3411, 3, '2016-01-27', '2016-01-28', '2016-02-17', 'HIP'),
(3416, 33, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),
(3416, 34, '2016-02-11', '2016-02-15', '2016-02-15', 'TAP'),
(3418, 33, '2016-01-06', '2016-01-08', '2016-01-11', 'TAP'),
(3418, 34, '2016-03-04', '2016-03-08', '2016-03-09', 'TAP'),
(3421, 36, '2016-01-29', '2016-02-02', '2016-02-04', 'TAP'),
(3421, 37, '2016-02-25', '2016-02-29', '2016-03-01', 'TAP'),
(3422, 5, '2016-02-05', '2016-02-05', '2016-02-05', 'HIP'),
(3431, 5, '2016-01-04', '2016-01-07', '2016-01-08', 'TAP'),
(3440, 3, '2016-02-08', '2016-02-10', '2016-02-10', 'TAP'),
(3441, 37, '2016-01-08', '2016-01-12', '2016-01-20', 'TAP'),
(3441, 38, '2016-02-03', '2016-02-04', '2016-02-05', 'TAP'),
(3443, 2, '2016-01-20', '2016-01-22', '2016-02-05', 'HIP'),
(3457, 7, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),
(3457, 6, '2016-01-25', '2016-01-27', '2016-02-09', 'TAP'),
(3459, 15, '2016-01-22', '2016-01-26', '2016-02-04', 'TAP'),
(3475, 19, '2016-01-20', '2016-01-25', '2016-02-03', 'TAP'),
(3475, 20, '2016-02-03', '2016-02-03', '2016-02-16', 'TAP'),
(3475, 21, '2016-03-14', '2016-03-16', '2016-03-16', 'TAP'),
(3476, 4, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),
(3479, 6, '2016-01-25', '2016-01-28', '2016-02-02', 'TAP'),
(3479, 7, '2016-02-14', '2016-02-16', '2016-02-17', 'TAP'),
(3479, 8, '2016-03-10', '2016-03-14', '2016-03-24', 'TAP'),
(3485, 13, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),
(3485, 14, '2016-02-11', '2016-02-15', '2016-02-23', 'TAP'),
(3486, 7, '2016-03-07', '2016-03-10', '2016-03-10', 'TAP'),
(3490, 20, '2016-03-17', '2016-03-18', '2016-03-18', 'TAP'),
(3490, 18, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),
(3490, 19, '2016-02-05', '2016-02-08', '2016-02-15', 'TAP'),
(3497, 3, '2016-02-08', '2016-02-08', '2016-02-08', 'HIP'),
(3498, 4, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),
(3498, 5, '2016-02-16', '2016-02-18', '2016-02-18', 'TAP'),
(3498, 6, '2016-03-01', '2016-03-02', '2016-03-02', 'TAP'),
(3499, 4, '2016-01-28', '2016-02-02', '2016-02-02', 'HIP'),
(3500, 6, '2016-01-12', '2016-01-12', '2016-01-13', 'TAP'),
(3500, 7, '2016-02-04', '2016-02-08', '2016-02-09', 'TAP'),
(3500, 8, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),
(3501, 3, '2016-01-29', '2016-02-01', '2016-02-12', 'HIP'),
(3502, 3, '2016-01-19', '2016-01-25', '2016-01-25', 'HIP'),
(3567, 8, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),
(3567, 9, '2016-03-03', '2016-03-04', '2016-03-04', 'TAP'),
(3567, 10, '2016-03-18', '2016-03-18', '2016-03-18', 'TAP'),
(3567, 7, '2016-01-15', '2016-01-18', '2016-01-18', 'TAP'),
(3607, 43, '2016-03-31', '2016-04-04', '2016-04-06', 'TAP'),
(3607, 41, '2016-02-02', '2016-02-04', '2016-02-04', 'TAP'),
(3607, 42, '2016-03-16', '2016-03-18', '2016-03-24', 'TAP'),
(3678, 13, '2016-01-13', '2016-01-18', '2016-02-03', 'TAP'),
(3678, 14, '2016-03-16', '2016-03-18', '2016-03-18', 'TAP'),
(3679, 7, '2016-01-29', '2016-02-02', '2016-02-02', 'TAP'),
(3686, 5, '2016-01-11', '2016-01-11', '2016-01-12', 'HIP'),
(3687, 19, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),
(3731, 35, '2016-01-14', '2016-01-18', '2016-01-20', 'TAP'),
(3731, 36, '2016-01-27', '2016-01-29', '2016-01-29', 'TAP'),
(3731, 37, '2016-02-10', '2016-02-12', '2016-02-19', 'TAP'),
(3731, 38, '2016-03-08', '2016-03-10', '2016-03-14', 'TAP'),
(3743, 5, '2016-02-01', '2016-02-04', '2016-02-17', 'TAP'),
(3752, 4, '2016-01-20', '2016-02-01', '2016-02-03', 'HIP'),
(3759, 4, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),
(3761, 4, '2016-02-16', '2016-02-29', '2016-03-02', 'TAP'),
(3761, 5, '2016-03-29', '2016-03-31', '2016-04-05', 'TAP'),
(3809, 4, '2016-01-13', '2016-01-18', '2016-01-29', 'HIP'),
(3836, 3, '2016-01-28', '2016-02-05', '2016-02-08', 'HIP'),
(3908, 5, '2016-02-08', '2016-02-16', '2016-02-23', 'HIP'),
(3915, 5, '2016-03-29', '2016-03-29', '2016-03-31', 'HIP'),
(3983, 2, '2016-01-05', '2016-01-05', '2016-01-19', 'HIP'),
(3984, 2, '2016-03-29', '2016-03-30', '2016-04-06', 'HIP'),
(3986, 5, '2016-03-15', '2016-03-16', '2016-03-18', 'TAP'),
(3986, 6, '2016-03-29', '2016-03-29', '2016-04-12', 'TAP'),
(4051, 1, '2016-02-29', '2016-03-01', '2016-03-01', 'HIP')
;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 Month_End,
[Prop Code], [History Ind],[Accepted by Allocations], [Let Date], Team
FROM @vacant v join dates d ON d.Month_End between [Accepted by Allocations] and isnull([Let Date], GETDATE())
Where Team = 'TAP' and [Prop Code] = '3459'
order by MOnth_End

That doesn't really help explain what you want as output. Forget the calendar table or trying to write a query. Can you just mock up a table of what the results should be when you run your query?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jaggy99 Ten Centuries Points: 1308 More actions · Answer 9

Please find below the desired output

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

Here is the valid link. https://app.box.com/s/eo6045eln5zwjm3jxs282ceefmh3w892

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

Maybe something like this?

SELECT month_end,

v.[Prop Code],

v.[History Ind],

v.[Accepted by Allocations],

v.[Let Date],

v.Team,

vacant_days=COUNT(1)

FROM @vacant v

CROSS APPLY

(

SELECT TOP (DATEDIFF(dd,[Accepted by Allocations], [Let Date])+1)

vacant_date=DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,[Accepted by Allocations])

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n1(n)

CROSS JOIN

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n2(n)

CROSS JOIN

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n3(n)

) vacant_dates

CROSS APPLY

(

SELECT month_end=CAST(DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,vacant_dates.vacant_date)+1,0)) AS DATE)

)month_end

GROUP BY month_end,

v.[Prop Code],

v.[History Ind],

v.[Accepted by Allocations],

v.[Let Date],

v.Team

ORDER BY v.[Prop Code],

v.[Accepted by Allocations];

I'm calculating the intervening dates and end of month dates on the fly instead of using a calendar table; with a calendar table you could just JOIN to it instead of doing the CROSS APPLYs.

Cheers!

jaggy99 Ten Centuries Points: 1308 More actions · Answer 12

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 VARCHAR(3))

INSERT INTO @vacant ([Prop Code], [History Ind], [Void Start Date], [Accepted by Allocations], [Let Date], Team) VALUES

(1043, 38, '2016-01-18', '2016-01-20', '2016-01-29', 'TAP'),(1043, 39, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),(1043, 40, '2016-02-26', '2016-03-01', '2016-03-01', 'TAP'),(1043, 41, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),(1045, 29, '2016-01-25', '2016-01-28', '2016-01-29', 'TAP'),(1045, 30, '2016-02-17', '2016-02-19', '2016-02-24', 'TAP'),

(1045, 31, '2016-03-11', '2016-03-15', '2016-03-18', 'TAP'),(2448, 36, '2016-01-11', '2016-01-13', '2016-01-19', 'TAP'),(2448, 37, '2016-03-01', '2016-03-03', '2016-03-04', 'TAP'),(2485, 35, '2016-02-12', '2016-02-16', '2016-02-19', 'TAP'),(2485, 33, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),(2485, 34, '2016-01-20', '2016-01-20', '2016-01-20', 'TAP'),

(2486, 37, '2016-03-10', '2016-03-14', '2016-03-15', 'TAP'),(2486, 35, '2016-01-14', '2016-01-15', '2016-01-15', 'TAP'),(2486, 36, '2016-02-15', '2016-02-17', '2016-02-19', 'TAP'),(2493, 30, '2016-01-12', '2016-01-13', '2016-01-13', 'TAP'),(2493, 31, '2016-01-27', '2016-01-28', '2016-01-28', 'TAP'),(2494, 37, '2016-03-08', '2016-03-10', '2016-03-16', 'TAP'),

(2494, 38, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),(2502, 47, '2016-02-18', '2016-02-22', '2016-02-22', 'TAP'),(2502, 45, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),(2502, 46, '2016-01-25', '2016-01-28', '2016-01-28', 'TAP'),(2510, 38, '2016-02-17', '2016-02-19', '2016-02-23', 'TAP'),(2510, 39, '2016-02-24', '2016-02-24', '2016-02-24', 'TAP'),

(2510, 36, '2016-01-18', '2016-01-19', '2016-01-19', 'TAP'),(2510, 37, '2016-02-04', '2016-02-08', '2016-02-11', 'TAP'),(2513, 39, '2016-01-12', '2016-01-14', '2016-01-15', 'TAP'),(2513, 40, '2016-03-10', '2016-03-14', '2016-03-16', 'TAP'),(2716, 4, '2016-01-04', '2016-01-04', '2016-01-07', 'HIP'),(2727, 35, '2016-02-18', '2016-02-22', '2016-02-24', 'TAP'),

(2727, 34, '2016-02-02', '2016-02-04', '2016-02-10', 'TAP'),(2756, 43, '2016-01-11', '2016-01-13', '2016-01-15', 'TAP'),(2756, 44, '2016-03-10', '2016-03-14', '2016-03-17', 'TAP'),(2841, 43, '2016-03-08', '2016-03-10', '2016-03-10', 'TAP'),(2841, 40, '2016-01-11', '2016-01-13', '2016-01-13', 'TAP'),(2841, 41, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),

(2841, 42, '2016-02-26', '2016-02-29', '2016-02-29', 'TAP'),(2842, 41, '2016-03-18', '2016-03-22', '2016-03-22', 'TAP'),(2842, 42, '2016-03-24', '2016-03-30', '2016-03-31', 'TAP'),(2842, 40, '2016-01-25', '2016-01-28', '2016-02-03', 'TAP'),(2843, 42, '2016-01-22', '2016-01-22', '2016-01-22', 'TAP'),(2843, 43, '2016-01-27', '2016-01-29', '2016-02-01', 'TAP'),

(2843, 44, '2016-02-22', '2016-02-24', '2016-02-24', 'TAP'),(2843, 45, '2016-03-11', '2016-03-15', '2016-03-23', 'TAP'),(2844, 47, '2016-03-21', '2016-03-23', '2016-03-23', 'TAP'),(2844, 48, '2016-03-31', '2016-04-04', '2016-04-04', 'TAP'),(2844, 44, '2016-02-05', '2016-02-08', '2016-02-08', 'TAP'),(2844, 45, '2016-02-12', '2016-02-16', '2016-02-18', 'TAP'),

(2844, 46, '2016-02-26', '2016-03-01', '2016-03-04', 'TAP'),(2844, 41, '2016-01-04', '2016-01-06', '2016-01-18', 'TAP'),(2844, 42, '2016-01-22', '2016-01-25', '2016-01-25', 'TAP'),(2844, 43, '2016-01-29', '2016-02-02', '2016-02-01', 'TAP'),(2845, 45, '2016-03-15', '2016-03-17', '2016-03-18', 'TAP'),(2845, 42, '2016-01-07', '2016-01-11', '2016-01-21', 'TAP'),

(2845, 43, '2016-02-05', '2016-02-09', '2016-02-10', 'TAP'),(2845, 44, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),(2997, 35, '2016-02-17', '2016-02-19', '2016-02-22', 'TAP'),(3009, 33, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),(3010, 36, '2016-02-03', '2016-02-04', '2016-02-04', 'TAP'),(3010, 37, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),

(3012, 2, '2016-02-22', '2016-03-04', '2016-03-08', 'HIP'),(3013, 5, '2016-03-24', '2016-03-24', '2016-03-31', 'HIP'),(3101, 37, '2016-02-01', '2016-02-03', '2016-02-09', 'TAP'),(3101, 38, '2016-02-16', '2016-02-18', '2016-02-19', 'TAP'),(3155, 35, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),(3216, 3, '2016-03-03', '2016-03-10', '2016-03-18', 'TAP'),

(3216, 4, '2016-03-21', '2016-03-23', '2016-03-24', 'TAP'),(3252, 40, '2016-02-29', '2016-03-02', '2016-03-02', 'TAP'),(3252, 41, '2016-03-14', '2016-03-17', '2016-03-17', 'TAP'),(3252, 42, '2016-03-23', '2016-03-24', '2016-03-29', 'TAP'),(3252, 37, '2016-01-05', '2016-01-06', '2016-01-08', 'TAP'),(3252, 38, '2016-02-01', '2016-02-03', '2016-02-03', 'TAP'),

(3252, 39, '2016-02-15', '2016-02-17', '2016-02-18', 'TAP'),(3381, 40, '2016-03-18', '2016-03-21', '2016-03-21', 'TAP'),(3381, 41, '2016-03-29', '2016-03-31', '2016-04-04', 'TAP'),(3381, 38, '2016-02-05', '2016-02-09', '2016-02-09', 'TAP'),(3381, 39, '2016-02-17', '2016-02-18', '2016-02-18', 'TAP'),(3383, 40, '2016-03-21', '2016-03-23', '2016-04-08', 'TAP'),

(3384, 35, '2016-01-11', '2016-01-11', '2016-01-11', 'TAP'),(3384, 36, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),(3384, 37, '2016-02-08', '2016-02-10', '2016-02-18', 'TAP'),(3384, 38, '2016-02-19', '2016-02-23', '2016-02-23', 'TAP'),(3385, 36, '2016-01-18', '2016-01-20', '2016-01-22', 'TAP'),(3385, 37, '2016-02-17', '2016-02-19', '2016-02-19', 'TAP'),

(3394, 3, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),(3407, 39, '2016-02-22', '2016-02-24', '2016-02-26', 'TAP'),(3411, 3, '2016-01-27', '2016-01-28', '2016-02-17', 'HIP'),(3416, 33, '2016-02-01', '2016-02-03', '2016-02-08', 'TAP'),(3416, 34, '2016-02-11', '2016-02-15', '2016-02-15', 'TAP'),(3418, 33, '2016-01-06', '2016-01-08', '2016-01-11', 'TAP'),

(3418, 34, '2016-03-04', '2016-03-08', '2016-03-09', 'TAP'),(3421, 36, '2016-01-29', '2016-02-02', '2016-02-04', 'TAP'),(3421, 37, '2016-02-25', '2016-02-29', '2016-03-01', 'TAP'),(3422, 5, '2016-02-05', '2016-02-05', '2016-02-05', 'HIP'),(3431, 5, '2016-01-04', '2016-01-07', '2016-01-08', 'TAP'),(3440, 3, '2016-02-08', '2016-02-10', '2016-02-10', 'TAP'),

(3441, 37, '2016-01-08', '2016-01-12', '2016-01-20', 'TAP'),(3441, 38, '2016-02-03', '2016-02-04', '2016-02-05', 'TAP'),(3443, 2, '2016-01-20', '2016-01-22', '2016-02-05', 'HIP'),(3457, 7, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),(3457, 6, '2016-01-25', '2016-01-27', '2016-02-09', 'TAP'),(3459, 15, '2016-01-22', '2016-01-26', '2016-02-04', 'TAP'),

(3475, 19, '2016-01-20', '2016-01-25', '2016-02-03', 'TAP'),(3475, 20, '2016-02-03', '2016-02-03', '2016-02-16', 'TAP'),(3475, 21, '2016-03-14', '2016-03-16', '2016-03-16', 'TAP'),(3476, 4, '2016-01-11', '2016-01-12', '2016-01-12', 'TAP'),(3479, 6, '2016-01-25', '2016-01-28', '2016-02-02', 'TAP'),(3479, 7, '2016-02-14', '2016-02-16', '2016-02-17', 'TAP'),

(3479, 8, '2016-03-10', '2016-03-14', '2016-03-24', 'TAP'),(3485, 13, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),(3485, 14, '2016-02-11', '2016-02-15', '2016-02-23', 'TAP'),(3486, 7, '2016-03-07', '2016-03-10', '2016-03-10', 'TAP'),(3490, 20, '2016-03-17', '2016-03-18', '2016-03-18', 'TAP'),(3490, 18, '2016-01-07', '2016-01-11', '2016-01-12', 'TAP'),

(3490, 19, '2016-02-05', '2016-02-08', '2016-02-15', 'TAP'),(3497, 3, '2016-02-08', '2016-02-08', '2016-02-08', 'HIP'),(3498, 4, '2016-01-29', '2016-02-02', '2016-02-10', 'TAP'),(3498, 5, '2016-02-16', '2016-02-18', '2016-02-18', 'TAP'),(3498, 6, '2016-03-01', '2016-03-02', '2016-03-02', 'TAP'),(3499, 4, '2016-01-28', '2016-02-02', '2016-02-02', 'HIP'),

(3500, 6, '2016-01-12', '2016-01-12', '2016-01-13', 'TAP'),(3500, 7, '2016-02-04', '2016-02-08', '2016-02-09', 'TAP'),(3500, 8, '2016-03-11', '2016-03-15', '2016-03-15', 'TAP'),(3501, 3, '2016-01-29', '2016-02-01', '2016-02-12', 'HIP'),(3502, 3, '2016-01-19', '2016-01-25', '2016-01-25', 'HIP'),(3567, 8, '2016-02-23', '2016-02-25', '2016-02-26', 'TAP'),

(3567, 9, '2016-03-03', '2016-03-04', '2016-03-04', 'TAP'),(3567, 10, '2016-03-18', '2016-03-18', '2016-03-18', 'TAP'),(3567, 7, '2016-01-15', '2016-01-18', '2016-01-18', 'TAP'),(3607, 43, '2016-03-31', '2016-04-04', '2016-04-06', 'TAP'),(3607, 41, '2016-02-02', '2016-02-04', '2016-02-04', 'TAP'),(3607, 42, '2016-03-16', '2016-03-18', '2016-03-24', 'TAP'),

(3678, 13, '2016-01-13', '2016-01-18', '2016-02-03', 'TAP'),(3678, 14, '2016-03-16', '2016-03-18', '2016-03-18', 'TAP'),(3679, 7, '2016-01-29', '2016-02-02', '2016-02-02', 'TAP'),(3686, 5, '2016-01-11', '2016-01-11', '2016-01-12', 'HIP'),(3687, 19, '2016-03-09', '2016-03-11', '2016-03-15', 'TAP'),(3731, 35, '2016-01-14', '2016-01-18', '2016-01-20', 'TAP'),

(3731, 36, '2016-01-27', '2016-01-29', '2016-01-29', 'TAP'),(3731, 37, '2016-02-10', '2016-02-12', '2016-02-19', 'TAP'),(3731, 38, '2016-03-08', '2016-03-10', '2016-03-14', 'TAP'),(3743, 5, '2016-02-01', '2016-02-04', '2016-02-17', 'TAP'),(3752, 4, '2016-01-20', '2016-02-01', '2016-02-03', 'HIP'),(3759, 4, '2016-01-11', '2016-01-11', '2016-01-13', 'HIP'),

(3761, 4, '2016-02-16', '2016-02-29', '2016-03-02', 'TAP'),(3761, 5, '2016-03-29', '2016-03-31', '2016-04-05', 'TAP'),(3809, 4, '2016-01-13', '2016-01-18', '2016-01-29', 'HIP'),(3836, 3, '2016-01-28', '2016-02-05', '2016-02-08', 'HIP'),(3908, 5, '2016-02-08', '2016-02-16', '2016-02-23', 'HIP'),(3915, 5, '2016-03-29', '2016-03-29', '2016-03-31', 'HIP'),

(3983, 2, '2016-01-05', '2016-01-05', '2016-01-19', 'HIP'),(3984, 2, '2016-03-29', '2016-03-30', '2016-04-06', 'HIP'),(3986, 5, '2016-03-15', '2016-03-16', '2016-03-18', 'TAP'),(3986, 6, '2016-03-29', '2016-03-29', '2016-04-12', 'TAP'),(4051, 1, '2016-02-29', '2016-03-01', '2016-03-01', 'HIP')

DECLARE @Property TABLE ([Prop Code] INT, Team VARCHAR(3), MonthEnd VARCHAR(23))

INSERT INTO @Property ([Prop Code], Team, MonthEnd) VALUES

(1043, 'TAP', '2016-01-31'),(1045, 'TAP', '2016-01-31'),(1714, 'HIP', '2016-01-31'),(2448, 'TAP', '2016-01-31'),(2485, 'TAP', '2016-01-31'),(2486, 'TAP', '2016-01-31'),(2493, 'TAP', '2016-01-31'),(2494, 'TAP', '2016-01-31'),(2502, 'TAP', '2016-01-31'),(2510, 'TAP', '2016-01-31'),(2513, 'TAP', '2016-01-31'),(2716, 'HIP', '2016-01-31'),(2727, 'TAP', '2016-01-31'),

(2756, 'TAP', '2016-01-31'),(2841, 'TAP', '2016-01-31'),(2842, 'TAP', '2016-01-31'),(2843, 'TAP', '2016-01-31'),(2844, 'TAP', '2016-01-31'),(2845, 'TAP', '2016-01-31'),(2997, 'TAP', '2016-01-31'),(3009, 'TAP', '2016-01-31'),(3010, 'TAP', '2016-01-31'),(3011, 'HIP', '2016-01-31'),(3012, 'HIP', '2016-01-31'),(3013, 'HIP', '2016-01-31'),(3047, 'HIP', '2016-01-31'),

(3072, 'HIP', '2016-01-31'),(3100, 'HIP', '2016-01-31'),(3101, 'TAP', '2016-01-31'),(3102, 'HIP', '2016-01-31'),(3155, 'TAP', '2016-01-31'),(3216, 'TAP', '2016-01-31'),(3252, 'TAP', '2016-01-31'),(3270, 'HIP', '2016-01-31'),(3376, 'HIP', '2016-01-31'),(3380, 'HIP', '2016-01-31'),(3381, 'TAP', '2016-01-31'),(3383, 'TAP', '2016-01-31'),(3384, 'TAP', '2016-01-31'),

(3385, 'TAP', '2016-01-31'),(3394, 'HIP', '2016-01-31'),(3395, 'HIP', '2016-01-31'),(3397, 'HIP', '2016-01-31'),(3398, 'HIP', '2016-01-31'),(3402, 'HIP', '2016-01-31'),(3407, 'TAP', '2016-01-31'),(3411, 'HIP', '2016-01-31'),(3412, 'HIP', '2016-01-31'),(3416, 'TAP', '2016-01-31'),(3418, 'TAP', '2016-01-31'),(3421, 'TAP', '2016-01-31'),(3422, 'HIP', '2016-01-31'),

(3426, 'HIP', '2016-01-31'),(3427, 'HIP', '2016-01-31'),(3430, 'HIP', '2016-01-31'),(3431, 'TAP', '2016-01-31'),(3440, 'TAP', '2016-01-31'),(3441, 'TAP', '2016-01-31'),(3442, 'TAP', '2016-01-31'),(3443, 'HIP', '2016-01-31'),(3444, 'HIP', '2016-01-31'),(3445, 'HIP', '2016-01-31'),(3446, 'HIP', '2016-01-31'),(3451, 'HIP', '2016-01-31'),(3453, 'HIP', '2016-01-31'),

(3457, 'TAP', '2016-01-31'),(3459, 'TAP', '2016-01-31'),(3460, 'HIP', '2016-01-31'),(3471, 'HIP', '2016-01-31'),(3474, 'HIP', '2016-01-31'),(3475, 'TAP', '2016-01-31'),(3476, 'TAP', '2016-01-31'),(3479, 'TAP', '2016-01-31'),(3485, 'TAP', '2016-01-31'),(3486, 'TAP', '2016-01-31'),(3487, 'HIP', '2016-01-31'),(3488, 'HIP', '2016-01-31'),(3490, 'TAP', '2016-01-31'),

(3497, 'HIP', '2016-01-31'),(3498, 'TAP', '2016-01-31'),(3499, 'HIP', '2016-01-31'),(3501, 'HIP', '2016-01-31'),(3502, 'HIP', '2016-01-31'),(3555, 'HIP', '2016-01-31'),(3567, 'TAP', '2016-01-31'),(3607, 'TAP', '2016-01-31'),(3615, 'HIP', '2016-01-31'),(3678, 'TAP', '2016-01-31'),(3679, 'TAP', '2016-01-31'),(3687, 'TAP', '2016-01-31'),(3688, 'HIP', '2016-01-31'),

(3694, 'HIP', '2016-01-31'),(3704, 'HIP', '2016-01-31'),(3729, 'HIP', '2016-01-31'),(3731, 'TAP', '2016-01-31'),(3743, 'TAP', '2016-01-31'),(3745, 'HIP', '2016-01-31'),(3746, 'HIP', '2016-01-31'),(3751, 'HIP', '2016-01-31'),(3752, 'HIP', '2016-01-31'),(3753, 'HIP', '2016-01-31'),(3755, 'HIP', '2016-01-31'),(3759, 'HIP', '2016-01-31'),(3760, 'HIP', '2016-01-31'),

(3764, 'HIP', '2016-01-31'),(3761, 'TAP', '2016-01-31'),(3770, 'HIP', '2016-01-31'),(3779, 'HIP', '2016-01-31'),(3785, 'HIP', '2016-01-31'),(3786, 'HIP', '2016-01-31'),(3799, 'HIP', '2016-01-31'),(3806, 'HIP', '2016-01-31'),(3809, 'HIP', '2016-01-31'),(3808, 'HIP', '2016-01-31'),(3810, 'TAP', '2016-01-31'),(3811, 'HIP', '2016-01-31'),(3812, 'HIP', '2016-01-31'),

(3813, 'HIP', '2016-01-31'),(3814, 'HIP', '2016-01-31'),(3836, 'HIP', '2016-01-31'),(3837, 'HIP', '2016-01-31'),(3845, 'HIP', '2016-01-31'),(3846, 'HIP', '2016-01-31'),(3878, 'HIP', '2016-01-31'),(3879, 'HIP', '2016-01-31'),(3881, 'HIP', '2016-01-31'),(3882, 'HIP', '2016-01-31'),(3885, 'HIP', '2016-01-31'),(3899, 'HIP', '2016-01-31'),(3908, 'HIP', '2016-01-31'),

(3915, 'HIP', '2016-01-31'),(3961, 'HIP', '2016-01-31'),(3972, 'HIP', '2016-01-31'),(3983, 'HIP', '2016-01-31'),(3984, 'HIP', '2016-01-31'),(3985, 'HIP', '2016-01-31'),(3986, 'TAP', '2016-01-31'),(3987, 'HIP', '2016-01-31'),(4014, 'HIP', '2016-01-31'),(4044, 'HIP', '2016-01-31'),(1043, 'TAP', '2016-02-29'),(1045, 'TAP', '2016-02-29'),(1714, 'HIP', '2016-02-29'),

(2448, 'TAP', '2016-02-29'),(2485, 'TAP', '2016-02-29'),(2486, 'TAP', '2016-02-29'),(2493, 'TAP', '2016-02-29'),(2494, 'TAP', '2016-02-29'),(2502, 'TAP', '2016-02-29'),(2510, 'TAP', '2016-02-29'),(2513, 'TAP', '2016-02-29'),(2716, 'HIP', '2016-02-29'),(2727, 'TAP', '2016-02-29'),(2756, 'TAP', '2016-02-29'),(2841, 'TAP', '2016-02-29'),(2842, 'TAP', '2016-02-29'),

(2843, 'TAP', '2016-02-29'),(2844, 'TAP', '2016-02-29'),(2845, 'TAP', '2016-02-29'),(2997, 'TAP', '2016-02-29'),(3009, 'TAP', '2016-02-29'),(3010, 'TAP', '2016-02-29'),(3011, 'HIP', '2016-02-29'),(3012, 'HIP', '2016-02-29'),(3013, 'HIP', '2016-02-29'),(3047, 'HIP', '2016-02-29'),(3072, 'HIP', '2016-02-29'),(3100, 'HIP', '2016-02-29'),(3101, 'TAP', '2016-02-29'),

(3102, 'HIP', '2016-02-29'),(3155, 'TAP', '2016-02-29'),(3216, 'TAP', '2016-02-29'),(3252, 'TAP', '2016-02-29'),(3270, 'HIP', '2016-02-29'),(3376, 'HIP', '2016-02-29'),(3380, 'HIP', '2016-02-29'),(3381, 'TAP', '2016-02-29'),(3383, 'TAP', '2016-02-29'),(3384, 'TAP', '2016-02-29'),(3385, 'TAP', '2016-02-29'),(3394, 'HIP', '2016-02-29'),(3395, 'HIP', '2016-02-29'),

(3397, 'HIP', '2016-02-29'),(3398, 'HIP', '2016-02-29'),(3402, 'HIP', '2016-02-29'),(3407, 'TAP', '2016-02-29'),(3411, 'HIP', '2016-02-29'),(3412, 'HIP', '2016-02-29'),(3416, 'TAP', '2016-02-29'),(3418, 'TAP', '2016-02-29'),(3421, 'TAP', '2016-02-29'),(3426, 'HIP', '2016-02-29'),(3427, 'HIP', '2016-02-29'),(3430, 'HIP', '2016-02-29'),(3431, 'TAP', '2016-02-29'),

(3442, 'TAP', '2016-02-29'),(3443, 'HIP', '2016-02-29'),(3444, 'HIP', '2016-02-29'),(3445, 'HIP', '2016-02-29'),(3446, 'HIP', '2016-02-29'),(3451, 'HIP', '2016-02-29'),(3453, 'HIP', '2016-02-29'),(3457, 'TAP', '2016-02-29'),(3459, 'TAP', '2016-02-29'),(3460, 'HIP', '2016-02-29'),(3471, 'HIP', '2016-02-29'),(3474, 'HIP', '2016-02-29'),(3475, 'TAP', '2016-02-29'),

(3476, 'TAP', '2016-02-29'),(3479, 'TAP', '2016-02-29'),(3485, 'TAP', '2016-02-29'),(3486, 'TAP', '2016-02-29'),(3488, 'HIP', '2016-02-29'),(3490, 'TAP', '2016-02-29'),(3498, 'TAP', '2016-02-29'),(3501, 'HIP', '2016-02-29'),(3502, 'HIP', '2016-02-29'),(3555, 'HIP', '2016-02-29'),(3567, 'TAP', '2016-02-29'),(3607, 'TAP', '2016-02-29'),(3615, 'HIP', '2016-02-29'),

(3678, 'TAP', '2016-02-29'),(3679, 'TAP', '2016-02-29'),(3687, 'TAP', '2016-02-29'),(3688, 'HIP', '2016-02-29'),(3694, 'HIP', '2016-02-29'),(3704, 'HIP', '2016-02-29'),(3729, 'HIP', '2016-02-29'),(3731, 'TAP', '2016-02-29'),(3743, 'TAP', '2016-02-29'),(3745, 'HIP', '2016-02-29'),(3746, 'HIP', '2016-02-29'),(3751, 'HIP', '2016-02-29'),(3752, 'HIP', '2016-02-29'),

(3753, 'HIP', '2016-02-29'),(3755, 'HIP', '2016-02-29'),(3759, 'HIP', '2016-02-29'),(3760, 'HIP', '2016-02-29'),(3764, 'HIP', '2016-02-29'),(3761, 'TAP', '2016-02-29'),(3770, 'HIP', '2016-02-29'),(3779, 'HIP', '2016-02-29'),(3785, 'HIP', '2016-02-29'),(3786, 'HIP', '2016-02-29'),(3799, 'HIP', '2016-02-29'),(3806, 'HIP', '2016-02-29'),(3809, 'HIP', '2016-02-29'),

(3808, 'HIP', '2016-02-29'),(3810, 'TAP', '2016-02-29'),(3811, 'HIP', '2016-02-29'),(3812, 'HIP', '2016-02-29'),(3813, 'HIP', '2016-02-29'),(3814, 'HIP', '2016-02-29'),(3836, 'HIP', '2016-02-29'),(3837, 'HIP', '2016-02-29'),(3845, 'HIP', '2016-02-29'),(3846, 'HIP', '2016-02-29'),(3878, 'HIP', '2016-02-29'),(3879, 'HIP', '2016-02-29'),(3881, 'HIP', '2016-02-29'),

(3882, 'HIP', '2016-02-29'),(3885, 'HIP', '2016-02-29'),(3899, 'HIP', '2016-02-29'),(3908, 'HIP', '2016-02-29'),(3915, 'HIP', '2016-02-29'),(3961, 'HIP', '2016-02-29'),(3972, 'HIP', '2016-02-29'),(3983, 'HIP', '2016-02-29'),(3984, 'HIP', '2016-02-29'),(3985, 'HIP', '2016-02-29'),(3986, 'TAP', '2016-02-29'),(3987, 'HIP', '2016-02-29'),(4014, 'HIP', '2016-02-29'),

(4044, 'HIP', '2016-02-29'),(4049, 'HIP', '2016-02-29'),(4051, 'HIP', '2016-02-29'),(1043, 'TAP', '2016-03-31'),(1045, 'TAP', '2016-03-31'),(1714, 'HIP', '2016-03-31'),(2448, 'TAP', '2016-03-31'),(2485, 'TAP', '2016-03-31'),(2486, 'TAP', '2016-03-31'),(2493, 'TAP', '2016-03-31'),(2494, 'TAP', '2016-03-31'),(2502, 'TAP', '2016-03-31'),(2510, 'TAP', '2016-03-31'),

(2513, 'TAP', '2016-03-31'),(2716, 'HIP', '2016-03-31'),(2727, 'TAP', '2016-03-31'),(2756, 'TAP', '2016-03-31'),(2841, 'TAP', '2016-03-31'),(2842, 'TAP', '2016-03-31'),(2843, 'TAP', '2016-03-31'),(2844, 'TAP', '2016-03-31'),(2845, 'TAP', '2016-03-31'),(2997, 'TAP', '2016-03-31'),(3009, 'TAP', '2016-03-31'),(3010, 'TAP', '2016-03-31'),(3011, 'HIP', '2016-03-31'),

(3012, 'HIP', '2016-03-31'),(3013, 'HIP', '2016-03-31'),(3047, 'HIP', '2016-03-31'),(3072, 'HIP', '2016-03-31'),(3100, 'HIP', '2016-03-31'),(3101, 'TAP', '2016-03-31'),(3102, 'HIP', '2016-03-31'),(3155, 'TAP', '2016-03-31'),(3216, 'TAP', '2016-03-31'),(3252, 'TAP', '2016-03-31'),(3270, 'HIP', '2016-03-31'),(3376, 'HIP', '2016-03-31'),(3380, 'HIP', '2016-03-31'),

(3381, 'TAP', '2016-03-31'),(3383, 'TAP', '2016-03-31'),(3384, 'TAP', '2016-03-31'),(3385, 'TAP', '2016-03-31'),(3394, 'HIP', '2016-03-31'),(3395, 'HIP', '2016-03-31'),(3397, 'HIP', '2016-03-31'),(3398, 'HIP', '2016-03-31'),(3402, 'HIP', '2016-03-31'),(3407, 'TAP', '2016-03-31'),(3411, 'HIP', '2016-03-31'),(3412, 'HIP', '2016-03-31'),(3416, 'TAP', '2016-03-31'),

(3418, 'TAP', '2016-03-31'),(3421, 'TAP', '2016-03-31'),(3426, 'HIP', '2016-03-31'),(3427, 'HIP', '2016-03-31'),(3431, 'TAP', '2016-03-31'),(3442, 'TAP', '2016-03-31'),(3443, 'HIP', '2016-03-31'),(3444, 'HIP', '2016-03-31'),(3445, 'HIP', '2016-03-31'),(3446, 'HIP', '2016-03-31'),(3451, 'HIP', '2016-03-31'),(3453, 'HIP', '2016-03-31'),(3457, 'TAP', '2016-03-31'),

(3459, 'TAP', '2016-03-31'),(3474, 'HIP', '2016-03-31'),(3475, 'TAP', '2016-03-31'),(3485, 'TAP', '2016-03-31'),(3488, 'HIP', '2016-03-31'),(3490, 'TAP', '2016-03-31'),(3498, 'TAP', '2016-03-31'),(3501, 'HIP', '2016-03-31'),(3502, 'HIP', '2016-03-31'),(3555, 'HIP', '2016-03-31'),(3567, 'TAP', '2016-03-31'),(3607, 'TAP', '2016-03-31'),(3678, 'TAP', '2016-03-31'),

(3679, 'TAP', '2016-03-31'),(3687, 'TAP', '2016-03-31'),(3688, 'HIP', '2016-03-31'),(3694, 'HIP', '2016-03-31'),(3704, 'HIP', '2016-03-31'),(3729, 'HIP', '2016-03-31'),(3731, 'TAP', '2016-03-31'),(3743, 'TAP', '2016-03-31'),(3745, 'HIP', '2016-03-31'),(3746, 'HIP', '2016-03-31'),(3751, 'HIP', '2016-03-31'),(3752, 'HIP', '2016-03-31'),(3753, 'HIP', '2016-03-31'),

(3755, 'HIP', '2016-03-31'),(3759, 'HIP', '2016-03-31'),(3760, 'HIP', '2016-03-31'),(3764, 'HIP', '2016-03-31'),(3761, 'TAP', '2016-03-31'),(3770, 'HIP', '2016-03-31'),(3779, 'HIP', '2016-03-31'),(3785, 'HIP', '2016-03-31'),(3786, 'HIP', '2016-03-31'),(3799, 'HIP', '2016-03-31'),(3806, 'HIP', '2016-03-31'),(3809, 'HIP', '2016-03-31'),(3808, 'HIP', '2016-03-31'),

(3810, 'TAP', '2016-03-31'),(3811, 'HIP', '2016-03-31'),(3812, 'HIP', '2016-03-31'),(3813, 'HIP', '2016-03-31'),(3814, 'HIP', '2016-03-31'),(3836, 'HIP', '2016-03-31'),(3837, 'HIP', '2016-03-31'),(3845, 'HIP', '2016-03-31'),(3846, 'HIP', '2016-03-31'),(3878, 'HIP', '2016-03-31'),(3879, 'HIP', '2016-03-31'),(3881, 'HIP', '2016-03-31'),(3882, 'HIP', '2016-03-31'),

(3885, 'HIP', '2016-03-31'),(3899, 'HIP', '2016-03-31'),(3908, 'HIP', '2016-03-31'),(3915, 'HIP', '2016-03-31'),(3961, 'HIP', '2016-03-31'),(3972, 'HIP', '2016-03-31'),(3983, 'HIP', '2016-03-31'),(3984, 'HIP', '2016-03-31'),(3985, 'HIP', '2016-03-31'),(3986, 'TAP', '2016-03-31'),(3987, 'HIP', '2016-03-31'),(4014, 'HIP', '2016-03-31'),(4044, 'HIP', '2016-03-31'),

(4049, 'HIP', '2016-03-31'),(4051, 'HIP', '2016-03-31')

;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()

),

Months As

(select DateAdd(month, -1, DateAdd(day, 1, Month_End)) As Month_Start, Month_End,[Prop Code], [History Ind],[Accepted by Allocations], [Let Date], Team

FROM @vacant v join dates d ON d.Month_End Between [Accepted by Allocations] and isnull(DateAdd(month, 1, [Let Date]), GETDATE())

)

Select *,

Case

When Month_Start >= [Accepted by Allocations] Then

Case When Month_End <= isnull([Let Date], GETDATE()) Then DATEDIFF(day, Month_Start, Month_End) + 1

Else DATEDIFF(day, Month_Start, isnull([Let Date], GETDATE())) + 1

End

Else

Case When Month_End <= isnull([Let Date], GETDATE()) Then DATEDIFF(day, [Accepted by Allocations], Month_End) + 1

Else DATEDIFF(day, [Accepted by Allocations], isnull([Let Date], GETDATE())) + 1

End

EndAs [Vacant Days],

(DATEDIFF(DAY,DATEADD(MONTH,-1,DATEADD(DAY,1,pro.MonthEnd)),pro.MonthEnd)+1) AS TotalDays,

((DATEDIFF(DAY,CASE WHEN DATEADD(MONTH,-1,DATEADD(DAY,1,pro.MonthEnd)) > [Accepted by Allocations] THEN DATEADD(MONTH,-1,DATEADD(DAY,1,pro.MonthEnd)) ELSE [Accepted by Allocations] END,COALESCE(CASE WHEN [Let Date] > pro.MonthEnd THEN pro.MonthEnd ELSE [Let Date] END,pro.MonthEnd))+0.0) /

(DATEDIFF(DAY,DATEADD(MONTH,-1,DATEADD(DAY,1,pro.MonthEnd)),pro.MonthEnd)+1.0)) * 100 AS [Vacancy Rate]

From Months

Join @Property pro on Months.[Prop Code] = pro.[Prop Code] AND months.Month_End = pro.MonthEnd

where months.[Prop Code] = '3411'

Order By Month_End

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 13

well assuming its supposed to be Vacant Days / Total days * 100, then your calculation is wrong I think

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jaggy99 Ten Centuries Points: 1308 More actions · Answer 14

jaggy99

Ten Centuries

Points: 1308

May 12, 2016 at 7:09 am

#1878567

Help then please. 🙂