Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Sum of hours by weekHi,
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!
Post Follow-up to this message
Re: Sum of hours by weekHi 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
Post Follow-up to this message Re: Sum of hours by weekOn 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)
Post Follow-up to this message Re: Sum of hours by weekHi 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
Post Follow-up to this message Re: Sum of hours by weekOn 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)
Post Follow-up to this message Re: Sum of hours by weekHi 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
Post Follow-up to this message Re: Sum of hours by weekOn 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)
Post Follow-up to this message Re: Sum of hours by weekHi 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
Post Follow-up to this message Re: Sum of hours by weekOn 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)
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
