Grouping data by week?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hemant Shah - 05 Jan 2006 16:58 GMT

Folks,

We have table that cointains timesheet entries for the employees.

Given user name, start date, and end date I want to get sum of hours for
each project, for each week.

Exmaple: Table looks like

UserName, Date, Project, Hours.

Suppose input is: shah, 2005-12-01, 2005-12-07.

2005-12-01 is on Thursday so I want to start from Sunday 2005-11-27,
2005-12-07 is on Wednesday so I want to end on Saturday 2005-12-10.

The output should look like:

Week of 2005-11-27

ProjectA 12
ProjectB 5
ProjectC 20

Week of 2005-12-04

ProjectA 21
ProjectB 15
ProjectC 0

Is there a DB2 function that will give me first day of week?
How can I group the sum of hours of the project by week?

Thanks.

Signature

Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Reply to this Message

Serge Rielau - 05 Jan 2006 17:27 GMT

> Folks,
>
[quoted text clipped - 30 lines]
>
> Thanks.

GROUP BY YEAR(dt), WEEK(dt)
Or week_iso() depending whether your week starts Sunday or Monday...

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Stefan Rybacki - 05 Jan 2006 20:10 GMT

>...
> GROUP BY YEAR(dt), WEEK(dt)
> Or week_iso() depending whether your week starts Sunday or Monday...
>
> Cheers
> Serge

On thing to mention. Looking at 01/01/2006 would give a wrong result, since the
year is 2006 but the week is 52 if the week starts on Monday.

Regards
Stefan

Reply to this Message

Serge Rielau - 05 Jan 2006 23:54 GMT

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 11 lines]
> Regards
> Stefan

True... I suppose a simple CASE-expression wrapping the YEAR() group
could solve this. An exercise for the attentive reader :-)

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Ian - 06 Jan 2006 04:34 GMT

> Folks,
>
[quoted text clipped - 11 lines]
> 2005-12-01 is on Thursday so I want to start from Sunday 2005-11-27,
> 2005-12-07 is on Wednesday so I want to end on Saturday 2005-12-10.

You can use the expression:

date_column - dayofweek_iso(date_column) days

to calculate the week's starting date (assuming weeks start on Sunday).

Reply to this Message

Hemant Shah - 09 Jan 2006 15:56 GMT

>> Folks,
>>
[quoted text clipped - 17 lines]
>
> to calculate the week's starting date (assuming weeks start on Sunday).

Thanks I will give it a try.

Signature

Reply to this Message