Need Help for Custom Report using months & weeks (Group by)

[WestEarn]

Hello,

Unfortunately, the report works wrong with the begining of a new month.

The report was good while we were in March. One row for the week of 12th of mars, one row fort the week of 19th, one row for the week of 26th.

Then, I played hands the 1st of April. Normally, the data should be cumulated in the row of the 26th, because Sunday is in the same week. But it works not like that. The hands played the Sunday 1st of April went on a new row, named April 26, 2012

I had a look at the column and the stat in the Stats configure window, but I don't find the error...

Have you got an idea ?

Thanks

SIncerely

[WestEarn]

I've found a new column statement that works:

Code: Select all

to_char(EXTRACT(year from (date_trunc('week', holdem_table_session_summary.date_start- interval '8 hour'))),'9999')||
to_char(EXTRACT(month from (date_trunc('week', holdem_table_session_summary.date_start- interval '8 hour'))),'99')||
to_char(EXTRACT(day from (date_trunc('week', holdem_table_session_summary.date_start- interval '8 hour'))),'99')

No need to set a format expression.

And if SO wants to work with week number like I wanted first, here is a more simple statement :

Code: Select all

extract(WEEK from holdem_table_session_summary.date_start- interval '8 hour')

Format expression:

Code: Select all

format_number(Week_nr, 0, true, false)

Note:
- interval '8 hour' statement is because I want to force my day end at 8:00 AM (set in preferences, but seems to be not considered by the query)

Bye

[kraada]

Thanks - anybody with a different offset would need to change that value (0 hours is midnight).