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)