Sum of hours by week

[mchen716]

Hi,
I have the following query:

SELECT p.employee_code, p.employee_name,
CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,
CONVERT(VARCHAR(12),t.post_date,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!

[Hugo Kornelis]

On 19 Apr 2005 14:38:49 -0700, mc******@gmail.com wrote:

Hi,
I have the following query:

SELECT p.employee_code, p.employee_name,
CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,
CONVERT(VARCHAR(12),t.post_date,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!

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_date,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]

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]

On 19 Apr 2005 15:01:13 -0700, mc******@gmail.com wrote:

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

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]

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]

On 21 Apr 2005 09:11:21 -0700, mc******@gmail.com wrote:

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

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]

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]

On 21 Apr 2005 14:39:48 -0700, mc******@gmail.com wrote:

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
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:
340 User 3/9/2005 3/18/2005 16.5
340 User 3/10/2005 4/4/2005 12.5

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]

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]

On 22 Apr 2005 07:55:04 -0700, mc******@gmail.com wrote:

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?
Hi Michelle,

Yes, that's correct.

Thanks again for your help.

You're welcome :-)

Best, Hugo
--

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