按工作日分开时间间隔

考虑分多个阶段处理您的需求：

1. 建立一个时间表表，其中包含所有可能的工作日开始时间（第一天6:00 AM）和结束点（第二天5:59 AM）。如果天数达到数百或数千，则可能需要使用VBA循环从范围的最早到最新日期动态创建一个新表（TableDef）。

   

2. 联合查询结合了所有三个可能的持续时间：a）一天的部分开始；b）整整一天；c）部分一天结束。每个都使用嵌套IIF()逻辑来计算小时数。每个SELECT查询将使用“实际小时数”表和“时间表”表运行交叉连接和间隔过滤器。

3. 横标签查询以在变换后的输出由分组集合WorkDates和ID，通过枢转状态列。

联合查询 （请确保删除注释，这些注释不符合Access SQL查询语句）。

-- ACTUAL START PARTIAL
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time], 
       s.WorkStartDates, s.WorkEndDates,
       IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
         DateDiff('n', w.[StartDate/Time], w.[End Date/Time]), 
         IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] >= s.WorkEndDates,
             DateDiff('n', w.[StartDate/Time], s.WorkEndDates), NULL)) / 60 AS WorkHours
FROM WorkCenterHours w, Schedule s
WHERE w.[StartDate/Time] BETWEEN s.WorkStartDates AND s.WorkEndDates

UNION

-- ACTUAL START AND END ACROSS WORKDAYS
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time], 
       s.WorkStartDates, s.WorkEndDates,
       DateDiff('n', s.WorkStartDates, s.WorkEndDates) / 60 As WorkHours
FROM WorkCenterHours w, Schedule s
WHERE s.WorkStartDates BETWEEN w.[StartDate/Time] AND w.[End Date/Time]
AND s.WorkEndDates BETWEEN w.[StartDate/Time] AND w.[End Date/Time]

UNION 

-- ACTUAL END LEFTOVER
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time],
       s.WorkStartDates, s.WorkEndDates,
       IIF(w.[StartDate/Time] <= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
         DateDiff('n', w.[End Date/Time], s.WorkEndDates), 
         IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
             DateDiff('n', w.[StartDate/Time], w.[End Date/Time]), NULL)) / 60 AS WorkHours
FROM WorkCenterHours w, Schedule s
WHERE w.[End Date/Time] BETWEEN s.WorkStartDates AND s.WorkEndDates;

交叉表查询 （用作数据源）

TRANSFORM Sum(q.WorkHours) AS SumOfWorkHours
SELECT DateValue(q.WorkStartDates) AS WorkDate, q.[Workcenter ID]
FROM UnionSourceQuery q
GROUP BY DateValue(q.WorkStartDates), q.[Workcenter ID]
PIVOT q.Status;

注意：由于使用嵌套逻辑和各种日期函数调用的并集查询中的交叉联接筛选器，因此不能保证性能，并且此解决方案的性能可能随数据大小而变化，然后最终受到影响。