Grouping Values into weeks in a adate rang with Linq to SQL or plain SQL

Question

I have a table that is of the form:

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

and is filled a bit like this:

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

What I need to produce is a table that gives a breakdown on a week by week basis of the number of hours worked for a given date range.

Ideally I'd do this in linq to sql, but if I need to call a stored procedure that's fine. I haven't really got the first clue how to go about implementing this.

How can I get started with this?

UPDATE

I've ended up writing this as a store procedure, which works, but to my eyes is not a pretty solution.

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

Is it possible to do this as a set based operation, or better still straight from linq to sql?

There are two rows with the same start date, will that always be the case? — N West, Apr 01 '11 at 11:39
The ranges could be anything, sometimes that will clash, they can start and end on any date. Each row represents an employees (sort of) expected working hours. — ilivewithian, Apr 01 '11 at 15:30
Ok, if I'm understanding you correctly, this table is at the granularity of "employee", each having a "start" and "end" date, and you want to aggregate the data to the level of, lets say, "company" and "week". The difficulty is that the source data defines a multi-dimensional value in a single field "hours per week", and you are transforming the data into two individual dimensions "hours" and "week". Then, in addition, you're aggregating the data by the week dimension. Your procedural logic seems to be the best solution to me. — N West, Apr 01 '11 at 19:18

score 0 · Answer 1 · edited May 23 '17 at 12:29

I'm not sure I understand what you expect the output to look like. But I'll try not to let that stop me from offering a suggestion.

If you build a calendar table, queries like this are generally easy to express. But what you're looking for might not be. (See below.)

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

Heres's part of the output. I trimmed it.

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

I've previously posted code for a basic calendar table (PostgreSQL syntax) on SO. It doesn't include ISO weeks and years. If you want, I'll post the full code here later. Just let me know.

Two things this query doesn't do

It doesn't try to calculate pro rata hours. For example, the last line of your sample input has a start date of 2010-03-05. That date falls in ISO week 9, which runs from 2010-03-01 to 2010-03-07; the query I wrote doesn't try to calculate 3/7 of 9 hours for ISO week 9.

It doesn't align with the calendar. ISO week numbers were convenient for me, because they were already in my calendar table. If you build your own calendar table, you can define them any way you like.

Grouping Values into weeks in a adate rang with Linq to SQL or plain SQL

1 Answers1