Sql-server – Days between two days by month SQL

sql serversql-server-2008-r2

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)

Best Answer

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.

Related Solutions

Sql-server – Best table schema to store daily hits for many items

I would want to attempt to insert a row for every hit and use SQL partitioning to store the table in managable chronological slices (by week or by month). This way, by keeping the original timestamp for each hit, you'll be able to aggregate the data using different methods as needed (by year one day, by hour the next, etc). For doing the aggregation, I would build a SSAS cube or Excel PivotTable, or T-SQL grouping functions. By using paritioning, you can store data for different time periods into individual filegroups spread across different disks to maintain scalability. When it comes to archival, you can perform what's called a sliding window to detach and later drop old unneeded time periods of data without expensive DELETE operations.

Sql-server – calculate days and equally distribute remaining

hope this works for you. It meets your expected results I believe:

| TASK |      BEGIN |        END | HOURS | WEEKDAYS |             M1H |             M2H |    M3H |    M4H |    M5H |    M6H |
|------|------------|------------|-------|----------|-----------------|-----------------|--------|--------|--------|--------|
|    1 | 2013-11-05 | 2013-11-05 |     5 |        1 |               5 |          (null) | (null) | (null) | (null) | (null) |
|    2 | 2013-10-01 | 2013-11-15 |     2 |       34 |  0.647058823529 |          (null) | (null) | (null) | (null) | (null) |
|    3 | 2013-11-15 | 2013-12-31 |    80 |       33 | 26.666666666667 | 53.333333333333 | (null) | (null) | (null) | (null) |
|    4 | 2013-05-01 | 2013-12-04 |     2 |      156 |  0.269230769231 |  0.038461538462 | (null) | (null) | (null) | (null) |
|    5 | 2013-07-01 | 2013-11-27 |    15 |      108 |  2.638888888889 |          (null) | (null) | (null) | (null) | (null) |
|    6 | 2013-11-01 | 2013-12-10 |    40 |       28 |              30 |              10 | (null) | (null) | (null) | (null) |


DECLARE @periodStart date, @periodEnd date

SET @periodStart = '2013-11-01'
SET @periodEnd   = '2014-05-01'

SELECT
          [task]
        , [begin]
        , [end]
        , [hours]
        , max(task_wd) AS weekdays
        , max( CASE WHEN yrmnth = ( YEAR(@periodStart) * 100 + MONTH(@periodStart) )                                   THEN ([hours] / task_wd) * mnth_wd END ) AS m1h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,1,@periodStart)) * 100 + MONTH(dateadd(MONTH,1,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m2h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,2,@periodStart)) * 100 + MONTH(dateadd(MONTH,2,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m3h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,3,@periodStart)) * 100 + MONTH(dateadd(MONTH,3,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m4h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,4,@periodStart)) * 100 + MONTH(dateadd(MONTH,4,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m5h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,5,@periodStart)) * 100 + MONTH(dateadd(MONTH,5,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m6h
FROM (
        SELECT
                  t.[task]
                , t.[begin]
                , t.[end]
                , t.[hours]
                , (year(c.d) * 100 + MONTH(c.d))                                 AS yrmnth

                , count(CASE
                         WHEN c.d >= @periodStart AND c.d < @periodEnd THEN c.d
                        END
                       ) over (partition BY t.[task], year(c.d), MONTH(c.d))     AS mnth_wd
                , count(c.d) over (partition BY t.[task]) * 1.0                  AS task_wd
        FROM Calendar AS c
        INNER JOIN tasks AS t ON c.d BETWEEN t.[begin] and t.[end]
        WHERE c.weekday = 1
        AND t.[begin] <= @periodEnd
        AND t.[end] >= @periodStart
     ) AS derived
GROUP BY
          [task]
        , [begin]
        , [end]
        , [hours]

I did this is here http://sqlfiddle.com/#!3/682ff/42 , but I also simulated your calendar table witha CTE so the code there is a bit messier.

Best Answer

Related Solutions

Sql-server – Best table schema to store daily hits for many items

Sql-server – calculate days and equally distribute remaining

Related Question