Grouping data by week?

[Hemant Shah]

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.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@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.

[Serge Rielau]

Hemant Shah wrote:

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.

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

Cheers
Serge

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

[Stefan Rybacki]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Serge Rielau wrote:

...
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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)

iD8DBQFDvX09yeCLzp/JKjARAgNRAJ9ZtPkXIYJFFdjnjsDjmp20gdjghACZAfce
YCbJ3zKrFPPFNDBnl2bsaQU=
=0SIN
-----END PGP SIGNATURE-----

[Serge Rielau]

Stefan Rybacki wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Serge Rielau wrote:

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

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

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

[Ian]

Hemant Shah wrote:

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.

You can use the expression:

date_column - dayofweek_iso(date_column) days

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

[Hemant Shah]

While stranded on information super highway Ian wrote:

Hemant Shah wrote:

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.

You can use the expression:

date_column - dayofweek_iso(date_column) days

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

Thanks I will give it a try.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@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.