On 19 Apr 2005 14:38:49 -0700, mchen716@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)