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

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]  @periodEnd)
    OR (a.[begin] >= @periodStart 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:

```
DE

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 = @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.

StackExchange Database Administrators Q#51293, answer score: 2