# calculate days and equally distribute remaining

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);

[begin] date,
[end] date,
[hours] decimal(6,3)
);

(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 SUM([weekday])
FROM #calendar
WHERE [d] BETWEEN a.[begin] AND a.[end]) AS weekdays
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 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
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```

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
, [begin]
, [end]
, [hours]
, 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.[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
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