Обсуждение: sorting by week?

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!

On Tue, Jul 30, 2002 at 11:13:42AM -0700, Phil Glatz wrote:
> 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?

[snip]

> 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

The way I usually acheive this is by saying sometihng like:

SELECT datefield - date_part('dow', datefield) as week, count(*)
FROM table
GROUP BY week;

Make sure you're using date fields, not datetime as this trick can do
strange things around the daylight savings transitions.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.