SEO

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

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

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

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

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

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