sql - 在SQL中遍历日期

Question

我有一个看起来像这样的数据表:

Name    StartTime              FinishTime              Work
Bob     2010-08-03 08:00:00    2010-08-03 12:00:00     4
Bob     2010-08-03 13:00:00    2010-08-03 16:00:00     3
Pete    2010-08-04 08:00:00    2010-08-04 12:00:00     4
Mark    2010-08-04 10:00:00    2010-08-04 12:00:00     2

这些日期范围都不应跨越午夜。
考虑到输入开始日期为2010-08-02和完成日期为2010-08-05，我想编写SQL以提供以下输出:

Date          Name   TotalWork
2010-08-03    Bob    7
2010-08-03    Pete   3
2010-08-04    Pete   4
2010-08-04    Mark   2 

我可以忍受，实际上最终可能需要，将没有工作关联的任何日子也显示在结果集中，也许像这样一行:

2010-08-05     NULL   0

我不太确定如何以与其他语言相同的方式遍历SQL中的日期。

为了提供一些上下文，此输出最终将插入Stacked Chart .Net控件中。

有人可以给我一个线索，教程的链接或其他帮助吗？否则，我想我会花几天时间来摆弄它!

谢谢!

乔纳森

Answer 1

试试这个:

Select DateAdd(day, 0, DateDiff(day, 0, StartDate)) Date,
    Name, Sum (Work) TotalWork
From TableData
Group By Name, DateAdd(day, 0, DateDiff(day, 0, StartDate)) 

难得的日子越来越难了。

   Declare @SD DateTime, @ED DateTime  -- StartDate and EndDate variables
   Select @SD = DateAdd(day, 0, DateDiff(day, 0, Min(StartDate))),
          @ED = DateAdd(day, 0, DateDiff(day, 0, Max(StartDate)))
   From TableData
   Declare @Ds Table (aDate SmallDateTime)
   While @SD <= @ED Begin 
       Insert @Ds(aDate ) Values @SD
       Set @SD = @SD + 1
   End 
-- ----------------------------------------------------
 Select DateAdd(day, 0, DateDiff(day, 0, td.StartDate)) Date,
    td.Name, Sum (td.Work) TotalWork
 From @Ds ds Left Join TableData td
    On DateAdd(day, 0, DateDiff(day, 0, tD.StartDate)) = ds.aDate 
 Group By Name, DateAdd(day, 0, DateDiff(day, 0, tD.StartDate)) 

编辑，我正在使用一种使用公用表表达式(CTE)的解决方案重新审视它。这不需要使用日期表。

    Declare @SD DateTime, @ED DateTime
    Declare @count integer = datediff(day, @SD, @ED)
    With Ints(i) As
      (Select 0 Union All
    Select i + 1 From Ints
    Where i < @count )  
     Select DateAdd(day, 0, DateDiff(day, 0, td.StartDate)) Date,
         td.Name, Sum (td.Work) TotalWork
     From Ints i 
        Left Join TableData d
           On DateDiff(day, @SD, d.StartDate) = i.i
     Group By d.Name, DateAdd(day, 0, DateDiff(day, 0, d.StartDate))