6

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. 1 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

1 Generate a set or sequence without loops

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • decided to improve on the logic for the 6 monthly columns which should now be solid.
    – Paul Maxwell
    Commented Oct 10, 2013 at 7:37
  • just in case, please note I didn't pay much attention to the where clause that I used - so you may want to revert to one you trust.
    – Paul Maxwell
    Commented Oct 10, 2013 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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