problem grouping with trunc() on datetime field

[Exie]

Hi Folks,

I have a query to sumarize data up, and I've been using the trunc() function to group by week.

My problem is, this only groups the weeks up until the end of the month. So my graph shows a 4x7day rows then one 2 or 3 day row for the end of that month.

How can I get a clean, consistant grouping of 7 day blocks ? When I graph the results in Excel, the looks all wonky because of the dips at the end of month.

My code currently looks like this:

select trunc(creat_dt,'WW') as "week", 
       orgn, 
       count(load_tm) 
  from cs_page_load_tm 
 where trunc(creat_dt,'WW') > trunc(sysdate,'WW') - (26 * 7) 
group by trunc(creat_dt,'WW') , orgn

 

[hoinz]

Hi,

in your code I cannot see anything referring to month.
So, if your query only gives you data from one month, the first idea will be that your table (within the given range of 26 weeks) contains only data from one month.

And please, could you explain the meaning of your column orgn ?
I wonder whether month is hidden in orgn.

regards

 

[Exie]

Hi,

There is definatly data covering a large time range (years).

The code above DOES return data for more than one month, however the trunc() function seems to be aligned with a given month.

For example, the date groups returned are like:
01MAR05 <-- Group contains 7 Days
08MAR05 <-- Group contains 7 Days
15MAR05 <-- Group contains 7 Days
22MAR05 <-- Group contains 7 Days
29MAR05 <-- Group contains 3 Days
01APR05 <-- Next group starts at 01 of the month, contains 7 days
08APR05 <-- Group contains 7 Days
15APR05 <-- Group contains 7 Days
22APR05 <-- Group contains 7 Days
29APR05 <-- Group contains 2 Days
01MAY05 <-- Next group starts at 01 of the month, contains 7 days
08MAY05 <-- Group contains 7 Days
22MAY05 <-- Group contains 7 Days
29MAY05 <-- Group contains 3 Days

... can you see how my weekly period is not even ? I'm trying to get an even 7 days in each group, regardless of if it breaks into the next month.

 

[hoinz]

ok, I see.
But I am not able to reproduce your problem.
One guess could be that the behaviour of TRUNC function is determined by some NLS variable; if this is correct, it should be in the docs, and you could find which setting you have to change.

Or may I suggest an alternative approach? You might use to_char(creat_dt,'WW') instead. This should give you the week of the year. (Unless the same settings affect to_char as well.)

select to_char(creat_dt,'WW') as "week", 
       orgn, 
       count(load_tm) 
  from cs_page_load_tm 
 where trunc(creat_dt,'WW') > trunc(sysdate,'WW') - (26 * 7) 
group by to_char(creat_dt,'WW') , orgn

hope this helps

 

[hoinz]

Actually I am able to reproduce your problem, but only if I use trunc(creat_dt,'W'), that is one W instead of two.
So please double-check your code, and count your Ws!