CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

How to group daily data on weekly basis using sql

I am trying to group the number of hours that employees worked for the last 4 weeks but I want to group them on a weekly basis. For example:

WEEK                    HOURS
Feb 24 to March 2         55
March 3 to March 9        40
March 10 to March 16      48
March 17 to March 23      37

This is what I have so far, please help. thanks

SET DATEFIRST 1

    SELECT CAST(MIN( [DT]) AS VARCHAR(20))+' TO '+CAST (MAX([DT]) AS VARCHAR(20)) AS DATE,
           SUM(HOURS) AS NUM_HRS
    FROM   MyTable
    GROUP BY DATEPART(WEEK,[DT])
    HAVING COUNT(DISTINCT[DT])=7

Upvotes: 0

Views: 4183

Answers (3)

user2989408
user2989408

Reputation: 3137

Try something like 

SELECT
    DATEADD(DD, 
            CONVERT(INT, (DATEDIFF(DD, '1/1/1900', t.DT)/7)) * 7, 
           '1/1/1900') [WeekBeginDate],
    DATEADD(DD,
            (CONVERT(INT, (DATEDIFF(DD, '1/1/1900', t.DT)/7)) * 7) + 6,
           '1/1/1900') [WeekEndDate],
    SUM(HOURS) AS NUM_HRS
FROM MyTable t
GROUP BY CONVERT(INT, DATEDIFF(DD, '1/1/1900', t.DT)/7)

Though this is the brute force trick, I think in your case it will work.

EDIT : Modified the query a little bit, the error was caused because of the order in which DATEDIFF calculates the difference.

Also here is a SQL FIDDLE with a working example.

EDIT 2 : Updated the Fiddle with the Date Format. To customize the date format, this article would help.

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

SET DATEFIRST 1

    SELECT DATEPART(WEEK,DT) AS WEEK,
           SUM(HOURS) AS NUM_HRS
    FROM   MyTable
    WHERE DT >= DATEADD(WEEK, -4, GetDate()),
    GROUP BY DATEPART(WEEK,[DT])

Upvotes: 1

dean
dean

Reputation: 10098

Create a Calendar auxilliary table, with Year, Month, Week, Date columns (you can also add holidays and other interesting stuff to it, it has many potential uses) and populate it for the period of interest.

After that, it's as easy as this:

SELECT sum(hours), cast(min(date) as varchar), cast(max(date) as varchar)
FROM Calendar c
LEFT OUTER JOIN MyTable h on h.Date = c.date
GROUP BY year, week
ORDER BY year, week

Upvotes: 1

Related Questions