Iterating through dates in SQL

Question

I have a table of data that looks a bit like this:

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

None of these date ranges should ever span over midnight.
I want to write SQL that will give me the following output, given an input Start Date of 2010-08-02 and a Finish Date of 2010-08-05

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

I could live with, and in fact may ultimately need, to have any days that do not have work associated also be represented in the results set, maybe as a row like this:

2010-08-05     NULL   0

I'm not quite sure how to iterate through dates in SQL in the same way that I would with other languages.

To give this some context, the output of this will ultimately plug into a Stacked Chart .Net control.

Could someone give me a clue, a link to a tutorial or some other help? Otherwise I think I'll be fiddling with this for days!

Thank you!

Jonathan

Answer 1

Try this:

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)) 

To get the missing days is harder.

   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)) 

EDIT, I am revisiting this with a solution that uses a Common Table Expression (CTE). This does NOT require use of a dates table.

    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)) 

Answer 2

The way that you iterate through rows in SQL is that you don't. SQL is a set-based language which requires a whole different mindset from other procedural languages. If you're going to be working with SQL you really need to be able to make that shift in thinking to be successful.

Here's how I would handle this one:

SELECT
    CONVERT(VARCHAR(10), StartTime, 121) AS [date],
    name,
    SUM(work)
FROM
    My_Table
WHERE
    StartTime >= @start_date AND
    StartTime < DATEADD(dy, 1, @finish_date)
GROUP BY
    CONVERT(VARCHAR(10), StartTime, 121),
    name

Also, your table design looks like it violates normal database design standards. Your "work" column is really just a calculation between the StartTime and FinishTime. That makes it a duplication of the same data, which can cause all sorts of problems. For example, what do you do when your StartTime and FinishTime are 4 hours apart, but the "Work" says 5 hours?

To include dates with no work associated, you'll need to either handle that in the front end, or you'll need a "Calendar" table. It would have all of the dates in it and you would do a LEFT JOIN to that with your table. For example:

SELECT
    CONVERT(VARCHAR(10), C.StartTime, 121) AS [date],
    MT.name,
    SUM(MT.work)
FROM
    Calendar C
LEFT JOIN My_Table MT ON
    MT.StartDate BETWEEN C.StartTime and C.FinishTime
WHERE
    C.StartTime >= @start_date AND
    C.StartTime < DATEADD(dy, 1, @finish_date)
GROUP BY
    CONVERT(VARCHAR(10), C.StartTime, 121),
    MT.name

The calendar table also allows you to add additional information to the dates, such as a flag for holidays, "overtime" days (maybe work counts as time and a half on Sundays), etc.

NOTE: Charles Bretana's solution is probably a little bit cleaner since it keeps the data types as datetimes instead of turning them into strings. I'm going to leave this here though for some of the other comments.