|
Home > Archive > Microsoft SQL Server forum > April 2005 > Sum of hours by week
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Sum of hours by week
|
|
| mchen716@gmail.com 2005-04-19, 8:23 pm |
| 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!
| |
| mchen716@gmail.com 2005-04-19, 8:23 pm |
| 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 2005-04-20, 8:23 pm |
| On 19 Apr 2005 15:01:13 -0700, mchen716@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@gmail.com 2005-04-21, 11:23 am |
| Hi Hugo,
Please see the below data:
Employee_code Employ
ee_name tran_date po
st_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 2005-04-21, 8:24 pm |
| On 21 Apr 2005 09:11:21 -0700, mchen716@gmail.com wrote:
>Hi Hugo,
>
>Please see the below data:
>
> Employee_code Employ
ee_name tran_date po
st_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@gmail.com 2005-04-21, 8:24 pm |
| 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 2005-04-21, 8:24 pm |
| On 21 Apr 2005 14:39:48 -0700, mchen716@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@gmail.com 2005-04-22, 11:23 am |
| 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 2005-04-22, 8:23 pm |
| On 22 Apr 2005 07:55:04 -0700, mchen716@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)
|
|
|
|
|