Sql-server – calculate days and equally distribute remaining

sql server

I'm having difficulty with calculating remaining days from hours being distributed over a date range within a given time period. For this example, the time period would be 2013-11-01 through 2013-05-01 (it will always be a 6 month period).

The calendar table has a 30 year set. ¹ Value of 1 for weekday indicates Monday – Friday.

Setup

CREATE TABLE #calendar (
  [d] date PRIMARY KEY,
  [weekday] tinyint DEFAULT 0,
);

INSERT INTO #calendar ([d]) VALUES
('2006-01-01', 0),
...
('2013-01-01', 1),
('2013-01-02', 1),
('2013-01-03', 1),
...
('2014-12-28', 0),
('2014-12-29', 1),
('2014-12-30', 1),
('2014-12-31', 1),
...
('2035-12-31', 1);

CREATE TABLE #tasks (
  [task] int PRIMARY KEY,
  [begin] date,
  [end] date,
  [hours] decimal(6,3)
);

INSERT INTO #tasks ([task], [begin], [end], [hours]) VALUES
(1, '2013-11-05', '2013-11-05', 5.0),
(2, '2013-10-01', '2013-11-15', 2.0),
(3, '2013-11-15', '2013-12-31', 80.0),
(4, '2013-05-01', '2013-12-04', 2.0),
(5, '2013-07-01', '2013-11-27', 15),
(6, '2013-11-01', '2013-12-10', 40),
(7, '2013-07-01', '2013-08-01', 50.0),
(8, '2014-06-01', '2014-07-01', 10.0);

Part 1

Getting the total weekdays for the tasks:

DECLARE @periodStart date, @periodEnd date;

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

SELECT a.[task], a.[begin], a.[end], a.[hours],
    (SELECT SUM([weekday])
    FROM #calendar
    WHERE [d] BETWEEN a.[begin] AND a.[end]) AS weekdays
FROM #tasks a
WHERE (a.[begin] < @periodStart AND a.[end] > @periodEnd)
    OR (a.[begin] >= @periodStart AND a.[end] < @periodEnd)
    OR (a.[begin] < @periodEnd AND a.[end] > @periodStart)

Result

task        begin      end        hours    weekdays
----------- ---------- ---------- -------  --------
1           2013-11-05 2013-11-05 5.000    1
2           2013-10-01 2013-11-15 2.000    34
3           2013-11-15 2013-12-31 80.000   33
4           2013-05-01 2013-12-04 2.000    156
5           2013-07-01 2013-11-27 15.000   108
6           2013-11-01 2013-12-10 40.000   28

Part 2

Getting the workdays for the time period:

DECLARE @periodStart date, @periodEnd date;

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

SELECT a.[task], a.[begin], a.[end], a.[hours],
    (SELECT SUM([weekday])
    FROM #calendar
    WHERE [d] BETWEEN a.[begin] AND a.[end]) AS weekdays,
    m1.weekdays AS m1wd,
    m2.weekdays AS m2wd,
    m3.weekdays AS m3wd,
    m4.weekdays AS m4wd,
    m5.weekdays AS m5wd,
    m6.weekdays AS m6wd
FROM #tasks a
CROSS JOIN
    -- Month 1 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+1, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+2, -1)) m1
CROSS JOIN
    -- Month 2 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+2, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+3, -1)) m2
CROSS JOIN
    -- Month 3 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+3, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+4, -1)) m3
CROSS JOIN
    -- Month 4 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+4, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+5, -1)) m4
CROSS JOIN
    -- Month 5 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+5, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+6, -1)) m5
CROSS JOIN
    -- Month 6 Weekdays
    (SELECT CAST(SUM([weekday]) AS decimal(3,1)) AS weekdays FROM #calendar
    WHERE [d] BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+6, 0) 
        AND DATEADD(MONTH, DATEDIFF(MONTH, 0, @periodStart)+7, -1)) m6
WHERE (a.[begin] < @periodStart AND a.[end] > @periodEnd)
    OR (a.[begin] >= @periodStart AND a.[end] < @periodEnd)
    OR (a.[begin] < @periodEnd AND a.[end] > @periodStart)

Result

task   begin      end        hours   weekdays    m1wd  m2wd  m3wd  m4wd  m5wd  m6wd
------ ---------- ---------- ------- ----------- ----- ----- ----- ----- ----- -----
1      2013-11-05 2013-11-05 5.000   1           22.0  23.0  20.0  21.0  22.0  22.0
2      2013-10-01 2013-11-15 2.000   34          22.0  23.0  20.0  21.0  22.0  22.0
3      2013-11-15 2013-12-31 80.000  33          22.0  23.0  20.0  21.0  22.0  22.0
4      2013-05-01 2013-12-04 2.000   156         22.0  23.0  20.0  21.0  22.0  22.0
5      2013-07-01 2013-11-27 15.000  108         22.0  23.0  20.0  21.0  22.0  22.0
6      2013-11-01 2013-12-10 40.000  28          22.0  23.0  20.0  21.0  22.0  22.0

Problem

Since each task can start/end at different times, how do I calculate and distribute the hours over the remaining days in the months? In part 2, would it be easier to just return a table between the period dates with the workdays and then pivot it?

Desired Result

task   begin      end        hours   weekdays    m1h       m2h       ...   m6h
------ ---------- ---------- ------- ----------- --------  --------  ----- ---
1      2013-11-05 2013-11-05 5.000   1           5         
2      2013-10-01 2013-11-15 2.000   34          0.647059  
3      2013-11-15 2013-12-31 80.000  33          26.66667  53.33333
4      2013-05-01 2013-12-04 2.000   156         0.269231  0.038462
5      2013-07-01 2013-11-27 15.000  108         2.638889  
6      2013-11-01 2013-12-10 40.000  28          30        10

¹ Generate a set or sequence without loops

Best Answer

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.

Related Solutions

Sql-server – How to calculate exact month (in days) difference b/w two dates

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT 
  DATEDIFF(YEAR,'20120101','20131231'), 
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'

Sql-server – the best way to get all data for a date range, plus the last event just before the range

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )