2

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)

0

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.

  • Absolutely no need to apologise when you are offering help. Thanks for your time! Conceptually, what is the WITH function at the beginning doing? Looking at it I would guess that it is taking the end part of the ModelEndDate (great stuff) and then calculating how many Months between the ModelEndDate and the date given to it. I am just slightly confused how that will then result in what I need, won't that just group the total free days by the month of the ModelEndDate? – user958551 Nov 2 '16 at 10:37
  • The WITH is a common table expression to avoid having to write the ModelEndMonth logic twice, once in the select list and once in the group by. ModelEndMonth was based on one of my listed assumptions as I wasn't sure from the question what you meant by month. So when you say DaysFreeSept, what is meant by Sept? i.e. what logic to you apply to your data to work out Sept? – Andy Jones Nov 2 '16 at 10:51
  • So, I currently just work out the TotalFreeDays across a few DateModels (each room actually has between 3 and 12 models), which is fairly simple, I check if the DepartureDate is between the start and end date of the model, if it is I sum the days between the DepartureDate and the ModelEndDate if not I sum the days between the ModelStartDate and ModelEndDate. I then group by room which produces a list of rooms and their Totalfreedays. I was just looking for some logic that could split that query into how many days were in march, how many in april etc. – user958551 Nov 2 '16 at 11:00
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.