使用 Linq to SQL 或纯 SQL 将日期范围内的值分组为周

Question

我有一个表格，其形式为：

StartDate[date], EndDate[date], HoursPerWeek[int]

其填充方式有点像这样：

StartDate  |   EndDate  | HoursPerWeek
01/01/2010 | 31/12/2010 | 37
01/01/2010 | 31/03/2010 | 16
05/03/2010 | 31/10/2010 | 9

我需要生成一个表格，该表格按周详细列出给定日期范围内的工作小时数。

理想情况下，我会在 linq to sql 中执行此操作，但如果我需要调用存储过程也没关系。我还没有真正了解如何实施这一点。

我该如何开始呢？

更新

我最终将其编写为存储过程，它有效，但在我看来这不是一个漂亮的解决方案。

CREATE PROCEDURE prcGetHoursWorked
(
    @StartDate DATE,
    @EndDate DATE
)
AS

CREATE TABLE #Results
(
    WeekStart DATE,
    TotalHours INT
)

DECLARE @WeekOffset INT
DECLARE @FirstDayOfStartWeek DATE
DECLARE @HoursThisWeek INT

SET @WeekOffset = 0
SET @FirstDayOfStartWeek = dbo.fnc_StartOfWeek(@StartDate, 2);

WHILE (DATEDIFF(wk, @StartDate, @EndDate) > @WeekOffset)
BEGIN
    SELECT @HoursThisWeek = SUM(HoursPerWeek) FROM StaffCost
    WHERE NOT (
                Start> DATEADD(wk, @WeekOffset + 1, @FirstDayOfStartWeek) 
                OR 
                [End] < DATEADD(wk, @WeekOffset, @FirstDayOfStartWeek)
            )

    INSERT INTO #Results
    VALUES(DATEADD(wk, @WeekOffset, @FirstDayOfStartWeek), @HoursThisWeek)

    SET @WeekOffset = @WeekOffset + 1
END

SELECT * FROM #Results

是否可以将其作为基于集合的操作来执行，或者更好地直接从 linq 到 sql？

Answer 1

我不确定我是否理解您期望的输出是什么样子。但我会尽力不让这阻止我提出建议。

如果您构建日历表，这样的查询通常很容易表达。但你正在寻找的可能不是。 （见下文。）

select c.cal_date as week_end, c.iso_year, c.iso_week, 
       sum(hoursperweek) as hours_this_week
from st
inner join calendar c on (c.cal_date >= startdate and 
                          c.cal_date <= enddate and 
                          c.day_of_week = 'Sun')
group by week_end, iso_year, iso_week
order by week_end

这是输出的一部分。我修剪了它。

week_end     iso_year   iso_week  hours_this_week
--
2010-01-03   2009       53        53
...
2010-02-21   2010        7        53
2010-02-28   2010        8        53
2010-03-07   2010        9        62
2010-03-14   2010       10        62
2010-03-21   2010       11        62
2010-03-28   2010       12        62
2010-04-04   2010       13        46
2010-04-11   2010       14        46
...

我之前发布过基本日历的代码SO 上的表 （PostgreSQL 语法）。它不包括 ISO 周和年。如果您愿意，我稍后会在这里发布完整的代码。请告诉我。

此查询不执行的两件事

它不会尝试按比例计算小时数。例如，示例输入的最后一行的开始日期为 2010-03-05。该日期属于 ISO 第 9 周，即从 2010 年 3 月 1 日到 2010 年 3 月 7 日；我编写的查询不会尝试计算 ISO 第 9 周 9 小时的 3/7。

它与日历不一致。 ISO 周数对我来说很方便，因为它们已经在我的日历表中了。如果您构建自己的日历表，则可以按照您喜欢的任何方式定义它们。