I have a new job where I am re-learning SQL (oh the joy) after years of not using it.
I currently have a table that looks something like this:
RoomNumber, BlockID, ModelEndDate, DepartureDate, DATEDIFF(DAY, StartDate, EndDate) AS NumDays
So essentially there is a model applied to each room, which has an enddate. If a resident leaves early we set the
DepartureDate and calculate how many days left in the Model.
EDIT Some background on how this actually is calculated (as I should have probably included it). Each room is occupied at different periods throughout the year, and has associated models to show what those dates are. E.g. one room may have the following:
ModelStartDate, ModelEndDate, Period
As a fairly standard rule of thumb, each room usually has three periods. I currently just calculate the total number of free days by either working out the difference between the start and end dates or (if departure is mid-model) I would work out the difference between the DepartureDate and ModelEndDate. I then group by Room giving me the totalfreedays.
What I now need to do is work out how these days are spread by month per block (many rooms to a block). So, if somebody was to Depart very early in the year, they would free up a load of days in each month for that room. For financial reporting I want to be able to say how many free days, per month, per block (summing all rooms within the Block). Ideally I would like a table that looks like the following:
BlockID, DaysFreeSept, DaysFreeOct, DaysFreeNov etc
I am having a little trouble understanding how I should accomplish this, I have looked through a few answers on here but they are mighty confusing.
Cheers for any help you can give me.
This is on SQL Server 2008 R2 (10.50)