Ygthkb

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)

Making the following assumptions:

• Month is calculated from ModelEndDate


• Days free is calculated by summing CAST(ModelEndDate - DepartureDate AS INT)

WITH room AS (

SELECT BlockID,

ModelEndMonth = DATEADD(MONTH,DATEDIFF(MONTH,'19000101',ModelEndDate),'19000101'),

DaysFree = DATEDIFF(DAY,DepartureDate,ModelEndDate))



SELECT BlockID,

ModelEndMonth,

DaysFree = SUM(DaysFree)

FROM dbo.YourTable

GROUP BY BlockID,

ModelEndMonth;

This code sample will get you part way there (apologies for any syntax errors but I am writing free-hand without a schema to query). You would then need to PIVOT the output to achieve the result-set in your required format. See PIVOT on Technet for an example.

This is a situation that might benefit from use of a date table. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Then you can join on the date table for all dates between the DepartureDate and the ModelEndDate, something like this:

Select y.BlockID,

    DaysFreeJan = Sum(CASE WHEN d.MonthName = 'January' THEN 1 END),

    DaysFreeFeb = Sum(CASE WHEN d.MonthName = 'February'THEN 1 END),

    ...

  From dbo.YourTable As y

  Join dbo.DateTable On d 

    On y.DepartureDate < d.DateKey

    And y.ModelEndDate >= d.DateKey

  Group By y.BlockID;

So each row of your table will join to multiple rows in your date table, then this query counts how many of those rows there are in each month.