Sum of hours by week

**Hugo Kornelis** · Jul 23 '05, 08:44 AM

Re: Sum of hours by week

On 19 Apr 2005 14:38:49 -0700, mchen716@gmail. com wrote:
[color=blue]
>Hi,
>I have the following query:
>
>SELECT p.employee_code , p.employee_name ,
>CONVERT(VARCHA R(12),t.tran_da te,101) AS TranDate,
>CONVERT(VARCHA R(12),t.post_da te,101) AS PostDate, SUM(tobill_hrs) AS
>TotalHours
>FROM tat_time t, hbm_persnl p
>WHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'
>AND '2005-03-31' AND p.employee_code IN ('0340')
>GROUP BY p.employee_code , p.employee_name , t.tran_date, t.post_date
>ORDER BY p.employee_code
>
>I would like to sum the tobill_hrs by week (Monday-Sunday) instead of
>by day. For instance the user will put in a date range (a from date
>and to date).
>
>Is this possible to do?
>
>Thanks![/color]

Hi mchen716,

You currently have two dates in your GROUP BY (tran_date and post_date);
for which one do you want to make a group for the whole week?

Assuming tran_date:

SELECT p.employee_code , p.employee_name ,
CONVERT(VARCHAR (12),MIN(t.tran _date),101) AS TranDate,
CONVERT(VARCHAR (12),t.post_dat e,101) AS PostDate,
SUM(tobill_hrs) AS TotalHours
FROM tat_time t,
hbm_persnl p
WHERE t.tk_empl_uno = p.empl_uno
AND t.tran_date BETWEEN '20050301' AND '20050331'
AND p.employee_code IN ('0340')
GROUP BY p.employee_code , p.employee_name , t.post_date,
DATEPART(week, t.tran_date)
ORDER BY p.employee_code

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**mchen716@gmail.com** · Jul 23 '05, 08:44 AM

Re: Sum of hours by week

Hi Hugo,

Thanks for the fast reply. It is now grouping the hours, but not by
week, it is now grouping the hours for every 3 days. Is there
something missing in the code?

Thanks!

0340 User 03/01/2005 03/17/2005 31.0000
0340 User 03/04/2005 03/18/2005 17.2500
0340 User 03/07/2005 03/18/2005 43.5000
0340 User 03/10/2005 04/04/2005 21.5000
0340 User 03/14/2005 03/18/2005 6.0000
0340 User 03/15/2005 04/04/2005 47.0000
0340 User 03/25/2005 03/28/2005 8.0000
0340 User 03/22/2005 04/04/2005 50.0000
0340 User 03/30/2005 03/28/2005 16.0000
0340 User 03/29/2005 04/04/2005 8.0000

**Hugo Kornelis** · Jul 23 '05, 08:44 AM

Re: Sum of hours by week

On 19 Apr 2005 15:01:13 -0700, mchen716@gmail. com wrote:
[color=blue]
>Hi Hugo,
>
>Thanks for the fast reply. It is now grouping the hours, but not by
>week, it is now grouping the hours for every 3 days. Is there
>something missing in the code?
>
>Thanks!
>
>0340 User 03/01/2005 03/17/2005 31.0000
>0340 User 03/04/2005 03/18/2005 17.2500
>0340 User 03/07/2005 03/18/2005 43.5000
>0340 User 03/10/2005 04/04/2005 21.5000
>0340 User 03/14/2005 03/18/2005 6.0000
>0340 User 03/15/2005 04/04/2005 47.0000
>0340 User 03/25/2005 03/28/2005 8.0000
>0340 User 03/22/2005 04/04/2005 50.0000
>0340 User 03/30/2005 03/28/2005 16.0000
>0340 User 03/29/2005 04/04/2005 8.0000[/color]

Hi mchen716,

The data above looks correct to me. In your original query, you were
grouping by BOTH tran_date and post_date. In my suggested alternative, I
changed one of them (tran_date, IIRC) to combine all entries from the
same week into one group, but leave the others intact.

Checking the data above, I see:

* For post_date 03/17/2005: tran_date 03/01/2005 only
* For post_date 03/18/2005: tran_dates 03/04/2005, 03/07/2005, and
03/14/2005. The time difference between the first and second is only
three days, but they did cross a date boundary (for simplicity, I simply
included the earliest day from the week for which data happened to be
present - obviously, there was no data for 03/01 through 03/03).
* For post_date 04/04/2005: tran_dates 03/10/2005, 03/15/2005,
03/22/2005, and 03/29/2005. Again, one date from each week (and
apparently no data for 03/08 or 03/09).

But obviously, your requirement was different from what I thought it
was. To better explain what you need, please post DDL (CREATE TABLE
statements), sample data (as INSERT statements) and expected output.
More details: www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**mchen716@gmail.com** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

Hi Hugo,

Please see the below data:

Employee_code Employee_name tran_date post_date totalhours
340 User 3/1/2005 3/17/2005 7.5
340 User 3/2/2005 3/17/2005 12.25
340 User 3/3/2005 3/17/2005 11.25
340 User 3/4/2005 3/18/2005 10
340 User 3/5/2005 3/18/2005 3
340 User 3/6/2005 3/18/2005 4.25
340 User 3/7/2005 3/18/2005 8
340 User 3/8/2005 3/18/2005 16.5
340 User 3/9/2005 3/18/2005 16.5
340 User 3/13/2005 3/18/2005 2.5
340 User 3/14/2005 3/18/2005 6
340 User 3/25/2005 3/28/2005 8
340 User 3/30/2005 3/28/2005 8
340 User 3/31/2005 3/28/2005 8
340 User 3/10/2005 4/4/2005 12.5
340 User 3/11/2005 4/4/2005 5
340 User 3/13/2005 4/4/2005 4
340 User 3/15/2005 4/4/2005 12.5
340 User 3/16/2005 4/4/2005 15.75
340 User 3/17/2005 4/4/2005 12.25
340 User 3/18/2005 4/4/2005 4.75
340 User 3/20/2005 4/4/2005 1.75
340 User 3/22/2005 4/4/2005 13.75
340 User 3/23/2005 4/4/2005 16
340 User 3/24/2005 4/4/2005 20.25
340 User 3/29/2005 4/4/2005 8

I would like to sum up the total hours by week with monday - sunday as
the week using tran_date.
For instance I the user enters the date range of 3/1/2005-3/31/2005.
The first week would be 2/28/2005 - 3/6/2005 and second week would be
3/7/2005 - 3/13/2005 and so on. The total hours for the first week
should be 48.25 and second should be 43.5
I hope this helps, thanks for all your help, I really appreciate it.

Thanks,
Michelle

**Hugo Kornelis** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

On 21 Apr 2005 09:11:21 -0700, mchen716@gmail. com wrote:
[color=blue]
>Hi Hugo,
>
>Please see the below data:
>
>Employee_cod e Employee_name tran_date post_date totalhours
>340 User 3/1/2005 3/17/2005 7.5
>340 User 3/2/2005 3/17/2005 12.25
>340 User 3/3/2005 3/17/2005 11.25
>340 User 3/4/2005 3/18/2005 10
>340 User 3/5/2005 3/18/2005 3
>340 User 3/6/2005 3/18/2005 4.25
>340 User 3/7/2005 3/18/2005 8
>340 User 3/8/2005 3/18/2005 16.5
>340 User 3/9/2005 3/18/2005 16.5
>340 User 3/13/2005 3/18/2005 2.5
>340 User 3/14/2005 3/18/2005 6
>340 User 3/25/2005 3/28/2005 8
>340 User 3/30/2005 3/28/2005 8
>340 User 3/31/2005 3/28/2005 8
>340 User 3/10/2005 4/4/2005 12.5
>340 User 3/11/2005 4/4/2005 5
>340 User 3/13/2005 4/4/2005 4
>340 User 3/15/2005 4/4/2005 12.5
>340 User 3/16/2005 4/4/2005 15.75
>340 User 3/17/2005 4/4/2005 12.25
>340 User 3/18/2005 4/4/2005 4.75
>340 User 3/20/2005 4/4/2005 1.75
>340 User 3/22/2005 4/4/2005 13.75
>340 User 3/23/2005 4/4/2005 16
>340 User 3/24/2005 4/4/2005 20.25
>340 User 3/29/2005 4/4/2005 8
>
>I would like to sum up the total hours by week with monday - sunday as
>the week using tran_date.
>For instance I the user enters the date range of 3/1/2005-3/31/2005.
>The first week would be 2/28/2005 - 3/6/2005 and second week would be
>3/7/2005 - 3/13/2005 and so on. The total hours for the first week
>should be 48.25 and second should be 43.5
>I hope this helps, thanks for all your help, I really appreciate it.
>
>Thanks,
>Michelle[/color]

Hi Michelle,

I can't test it (since you didn't post the data as INSERT statements,
nor a CREATE TABLE statement), but I think that this will do the job:

SELECT p.employee_code , p.employee_name ,
CONVERT(VARCHAR (12),MIN(t.tran _date),101) AS TranDate
SUM(tobill_hrs) AS TotalHours
FROM tat_time t,
hbm_persnl p
WHERE t.tk_empl_uno = p.empl_uno
AND t.tran_date BETWEEN '20050301' AND '20050331'
AND p.employee_code IN ('0340')
GROUP BY p.employee_code , p.employee_name ,
DATEPART(week, t.tran_date)
ORDER BY p.employee_code

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**mchen716@gmail.com** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

Hi Hugo,

I'm sorry I didn't post the data correctly. The script seems to work
now. I noticed that you took out the post date. Is that the only way
that this script can work?

Thanks,
Michelle

**Hugo Kornelis** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

On 21 Apr 2005 14:39:48 -0700, mchen716@gmail. com wrote:
[color=blue]
>Hi Hugo,
>
>I'm sorry I didn't post the data correctly. The script seems to work
>now. I noticed that you took out the post date. Is that the only way
>that this script can work?
>
>Thanks,
>Michelle[/color]

Hi Michelle,

It's not the only way, but you have to do _something_ with post date,
you can't just leace it as it was in the SELECT. Check the following two
rows from your sample:
[color=blue]
>340 User 3/9/2005 3/18/2005 16.5
>340 User 3/10/2005 4/4/2005 12.5[/color]

Both have a tran_date in the same week, so they should be combined. But
they have a different post_date. Which one would you want to show?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**mchen716@gmail.com** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

Hi Hugo,

I believe that the user would want to see the tran_date group instead
of the post_date. If they want to see it grouped by post_date then I
could just put post_date instead of tran_date in the script right?
Thanks again for your help.

Michelle

**Hugo Kornelis** · Jul 23 '05, 08:45 AM

Re: Sum of hours by week

On 22 Apr 2005 07:55:04 -0700, mchen716@gmail. com wrote:
[color=blue]
>Hi Hugo,
>
>I believe that the user would want to see the tran_date group instead
>of the post_date. If they want to see it grouped by post_date then I
>could just put post_date instead of tran_date in the script right?[/color]

Hi Michelle,

Yes, that's correct.

[color=blue]
>Thanks again for your help.[/color]

You're welcome :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sum of hours by week

Sum of hours by week

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment