sql - 计算重叠时段的小时/天总和，SQL

Question

我有一个表格，其中包含事件的开始日期 (event.startdate)、结束日期 (event.enddate) 和小时/人 (event.hrday)。我有另一个列出工作日的表，每个人都有另一个字段(calendar.name)。我想用每天的总工作时间填充这些列。如果两个事件在日期上重叠，我似乎无法弄清楚如何正确计算小时数，我只能为一个时间段内的单个事件得出正确的值。

我相信理论上这个问题有我需要的答案:compute sum of values associated with overlapping date ranges

但我是 SQL 的新手，即使经过一些额外的研究，我也不能从根本上理解发布的解决方案。我正在使用 Access 2013。如果这是一个 super 基本的问题，我深表歉意，我希望我想做的事情可以用 Access“直观地”处理...

我有什么:(“事件”表)

Startdate | Enddate    |  Hrsday   | Name
 5/1/2015    5/12/2015     1.25      Joe
 5/7/2015    5/8/2015       8        Joe

我在找什么:(“日历”表，第一列中已经填写的天数)

Weekdays | Joe | name2 | name3 | ....
5/1/2015   1.25
5/4/2015   1.25
5/5/2015   1.25
5/6/2015   1.25
5/7/2015   9.25
5/8/2015   9.25
5/11/2015  1.25
5/12/2015  1.25 

我已经尝试使用访问权限中的查询构建器来构建更新查询，但我的结果要么根本没有出现(没有更新，全部为空)，要么只会填充一个没有重叠的事件。 (5/1-5/12都有1.25)。

Answer 1

如果您希望能够在 MS-Access 中获得此类结果(不使用窗口函数)，我认为您需要创建一个“日期表”。

这是一个简单的例子，说明这在 SQL Server 中如何工作，但只使用 MS-Access 可用的语法(我希望如此)。

--Load the test data into a table variable
DECLARE @event TABLE (
    [start_date] DATE,
    end_date DATE,
    hrsperday NUMERIC(19,2),
    name VARCHAR(20));
INSERT INTO @event SELECT '20150401', '20150412', 1.25, 'Joe';
INSERT INTO @event SELECT '20150407', '20150408', 8, 'Joe';

--Add some more test data, to make it more "interesting"
INSERT INTO @event SELECT '20150401', '20150405', 0.1, 'Bill';
INSERT INTO @event SELECT '20150401', '20150430', 7.5, 'Bill';
INSERT INTO @event SELECT '20150412', '20150415', 0.5, 'Bill';

--Make a date table, this creates one on the fly but wouldn't work in MS-Access
--I store a date for each day in 2015/Apr, obviously I would want more dates eventually
DECLARE @dates TABLE (
    [date] DATE);
WITH cte AS (
    SELECT CONVERT(DATE, '20150401') AS [date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [date]) FROM cte WHERE [date] < '20150430')
INSERT INTO
    @dates
SELECT
    [date]
FROM
    cte OPTION (MAXRECURSION 0);

--Now the answer is trivial
SELECT
    e.name,
    d.[date],
    SUM(hrsperday) AS hrs
FROM
    @dates d
    LEFT JOIN @event e ON d.[date] BETWEEN e.[start_date] AND e.end_date
GROUP BY
    e.name,
    d.[date]
ORDER BY
    e.name,
    d.[date];

--Note the format you want, but a PIVOT would give you this
--(I don't think PIVOT is supported by MS-Access though)

结果是:

name    date    hrs
Bill    2015-04-01  7.60
Bill    2015-04-02  7.60
Bill    2015-04-03  7.60
Bill    2015-04-04  7.60
Bill    2015-04-05  7.60
Bill    2015-04-06  7.50
Bill    2015-04-07  7.50
Bill    2015-04-08  7.50
Bill    2015-04-09  7.50
Bill    2015-04-10  7.50
Bill    2015-04-11  7.50
Bill    2015-04-12  8.00
Bill    2015-04-13  8.00
Bill    2015-04-14  8.00
Bill    2015-04-15  8.00
Bill    2015-04-16  7.50
Bill    2015-04-17  7.50
Bill    2015-04-18  7.50
Bill    2015-04-19  7.50
Bill    2015-04-20  7.50
Bill    2015-04-21  7.50
Bill    2015-04-22  7.50
Bill    2015-04-23  7.50
Bill    2015-04-24  7.50
Bill    2015-04-25  7.50
Bill    2015-04-26  7.50
Bill    2015-04-27  7.50
Bill    2015-04-28  7.50
Bill    2015-04-29  7.50
Bill    2015-04-30  7.50
Joe 2015-04-01  1.25
Joe 2015-04-02  1.25
Joe 2015-04-03  1.25
Joe 2015-04-04  1.25
Joe 2015-04-05  1.25
Joe 2015-04-06  1.25
Joe 2015-04-07  9.25
Joe 2015-04-08  9.25
Joe 2015-04-09  1.25
Joe 2015-04-10  1.25
Joe 2015-04-11  1.25
Joe 2015-04-12  1.25