我在ms server management studio中无法达到所需的结果。
我有一个sql表，它记录机器上进程的开始和结束时间，我想使用这些数据来报告轮班性能统计信息。下表所示：

我想每天报告以下统计数字：

我可以使用以下代码在一天内成功实现这一点：

SELECT 
DATENAME(dw, CAST(PAINTING_END AS DATE)) [Day], 
CAST(PAINTING_END AS DATE) [Date], 
MIN(PAINTING_START) [Start],
MAX(PAINTING_END) [Finish],
DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START)) as 'Time (m)', 
CAST((CAST(DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START))AS DECIMAL) / 60) * 40 AS INT) as 'Target',
Count(PAINTING_END) [Output],
COUNT(PAINTING_END) * 100 / CAST((CAST(DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START))AS DECIMAL) / 60) * 40 AS INT) as 'Productivity' 
FROM p_statistics
WHERE PAINTING_START >= '2020-07-20 00:00:00' AND PAINTING_END <= '2020-07-20 23:59:59' 
GROUP BY CAST(PAINTING_END AS DATE)

然而，我的目标是在一周的开始和结束日期之间的每一天实现这些统计。
我用下面的代码尝试了这个方法，但结果中的值不正确：

SELECT 
DATENAME(dw, CAST(PAINTING_END AS DATE)) [Day], 
CAST(PAINTING_END as DATE) [Date], 
MIN(PAINTING_START) [Start],
MAX(PAINTING_END) [Finish], 
DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START)) as 'Time (m)', 
CAST((CAST(DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START))AS DECIMAL) / 60) * 40 AS INT) as 'Target',
Count(PAINTING_END) [Output],
COUNT(PAINTING_END) * 100 / CAST((CAST(DATEDIFF(minute, MIN(PAINTING_START), MAX(PAINTING_START))AS DECIMAL) / 60) * 40 AS INT) as 'Productivity' 
FROM p_statistics
WHERE PAINTING_START between '2020-07-20' AND '2020-07-26' AND PAINTING_END between '2020-07-20' AND '2020-07-26' 
GROUP BY CAST(PAINTING_END as DATE) 
ORDER BY CAST(PAINTING_END as DATE) ASC

您可以看到，选择多天时，开始和结束时间现在不正确。
有人能帮我实现目标吗？
非常感谢！