What's the best way to group items for weekly summaries? I can group by the
week of the month, or the week of the year -- suppose I wanted to make a
report for each week of each month, with most months ending with a partial
week - is this commonly done, or is the week of the year the most common
format?
I'm currently grouping by aliases like
TO_CHAR(date_field,'WW MM/DD/YY') AS fday
and stripping out the week and date parts when I display them
I also tried doing a by day grouping to check the values returned by the
'W' and 'WW' formats, with TO_CHAR(lt_modified,'MM/DD/YY W WW') AS fday
05/31/02 4 21 | 5
06/01/02 0 21 | 6
06/02/02 1 22 | 3
Why is there a zero for the month week on 6/1?
What I'd really like is an easy way to display counts of rows in each week
of the month, and be able to easily indicate the starting day of the week,
i.e.
Week | Count
06/01/02 | 147
06/08/02 | 118
06/15/02 | 161
06/23/02 | 138
06/29/02 | 27
Can this be done in pure SQL? I'm using 7.0.3
thanks!