-1


I need to get number of vacant days in each month in every year Leasing Contracts
So, my target is to occupy all units along the year.
For that I have to find how many days in each month units are not occupied (There s not contract) and focus on these units to reduce the price or give promotions.

So, I have lease start and lease expiry dates for the units in the first table and I want to know in summary table (second table) number of days in each month which are not belong to any of the leasing period (from/to range) from the first table for each unit.

I need Excel formulas and/or SQL scripts to be applied to find all days without rent for each project/unit in the selected year

Appreciate your support in advance!

Project Name    Unit No  Lease Start    Lease Expiry    Rent Per Annum
Building1       Unit1    01-01-2017     31-12-2017      70000
Building1       Unit1    01-01-2018     15-08-2018      60000
Building1       Unit1    01-10-2018     31-12-2018      60000
Building1       Unit2    01-01-2017     31-12-2017      60000
Building1       Unit2    01-03-2018     31-07-2018      60000
Building1       Unit2    01-09-2018     30-09-2018      45000



Vacancies Days multiply by last daily rate renting (rent per annum)/365

Project Name   Unit No    Jan-18  Feb-18   Aug-18          Sep-18    Oct-18
Building1      Unit1      0       0        16*164          30*164    0 
Building1      Unit2      31*164  28*164   31*164           0        31*123
MohamedZaatari
  • 434
  • 4
  • 5
  • Hi i think you can get excepted result by using dynamic SQL pivot query. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Sanpas Jan 24 '19 at 09:30
  • Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Michał Turczyn Jan 24 '19 at 09:36
  • Sounds like a typical gaps/islands problems to get the basic information. Once you have that, it becomes a pivot. I suspect you will be better served using numeric year/month column names as they will sort naturally. This will be quite complex. – SMor Jan 24 '19 at 12:52
  • @pascalsanchez actually I'm not for pivot table, I just want to find missing dates in the ranges which are not part of any lease period for example Unit1 has two leases in 2018 from 01-01-2018 to 15-08-2018 and from 01-10-2018 to 31-12-2018 so the unit was not occupied for 46 days in between. and these distributed as 16 days in August and 30 days in September! – MohamedZaatari Jan 26 '19 at 04:43

1 Answers1

0

I would use a calendar table.

This you join with your lease table to get whether a particular day is leased or not.

Then you can group by unit and month and do a pivot to get the result you want.

PhilS
  • 624
  • 3
  • 5