X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 2D9DF9FA147
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  6 Jul 2006 12:49:46 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 59415-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  6 Jul 2006 12:49:33 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.184])
	by postgresql.org (Postfix) with ESMTP id 580C49FA0EC
	for <pgsql-sql@postgresql.org>; Thu,  6 Jul 2006 12:49:32 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id p46so307695nfa
	for <pgsql-sql@postgresql.org>; Thu, 06 Jul 2006 08:49:31 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=i6E2fhSGP2a7ytHoH3E5Njg1Y4qyRvBEFOAxLS7l8YdALWlM8pQkEpwYrOCCyBjV+bShHwpnicgJpXVKMkwFiIiPMi8C2f3UZlTwfdtXIUE8tyMaEhsiZ9rB23U8MEBtHK+hSSqcx5QlKoriJ6EvoFyKQWG5KL6j96Dp/lQSWTE=
Received: by 10.78.117.10 with SMTP id p10mr262261huc;
	Thu, 06 Jul 2006 08:49:31 -0700 (PDT)
Received: by 10.78.29.2 with HTTP; Thu, 6 Jul 2006 08:49:31 -0700 (PDT)
Message-ID: <bf05e51c0607060849r6b76c74eq8a13054da4614c64@mail.gmail.com>
Date: Thu, 6 Jul 2006 10:49:31 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: KeithW@narrowpathinc.com
Subject: Re: week ending
Cc: pgsql-sql@postgresql.org
In-Reply-To: <44AC8F6A.4090704@NarrowPathInc.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_94177_27005169.1152200971025"
References: <44AC8F6A.4090704@NarrowPathInc.com>
X-Google-Sender-Auth: 6c838d185ed1972c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/65
X-Sequence-Number: 25639

------=_Part_94177_27005169.1152200971025
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/5/06, Keith Worthington <KeithW@narrowpathinc.com> wrote:
>
> Hi All,
>
> I just finished writing a query that groups data based on the week number.
>
> SELECT EXTRACT(week FROM col_a) AS week_number,
>         sum(col_b) AS col_b_total
>    FROM foo
>   WHERE foobar
>   GROUP BY EXTRACT(week FROM col_a)
>   ORDER BY EXTRACT(week FROM col_a);
>
> I would like to generate the starting date or ending date based on this
> number.  IOW instead of telling the user "week number" which they won't
> understand I would like to provide either Friday's date for "week
> ending" or Monday's date for "week beginning".
>
> SELECT <something> AS week_ending,
>         sum(col_b) AS col_b_total
>    FROM foo
>   WHERE foobar
>   GROUP BY EXTRACT(week FROM col_a)
>   ORDER BY EXTRACT(week FROM col_a);


Try this.  It puts Saturday as the Friday before it and Sunday as the Firday
after so if you want Saturday or Sunday to be on different weeks you will
need to do a little tweaking but this should get you going.

SELECT
   date_trunc('day', col_a + (interval '1 day' * (5 - extract(dow from
col_a)))) AS week_ending,
   sum(col_b) AS col_b_total
FROM foo
GROUP BY
   date_trunc('day', col_a + (interval '1 day' * (5 - extract(dow from
col_a))))

------=_Part_94177_27005169.1152200971025
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/5/06, <b class="gmail_sendername">Keith Worthington</b> &lt;<a href="mailto:KeithW@narrowpathinc.com">KeithW@narrowpathinc.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi All,<br><br>I just finished writing a query that groups data based on the week number.<br><br>SELECT EXTRACT(week FROM col_a) AS week_number,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sum(col_b) AS col_b_total<br>&nbsp;&nbsp; FROM foo<br>&nbsp;&nbsp;WHERE foobar<br>&nbsp;&nbsp;GROUP BY EXTRACT(week FROM col_a)
<br>&nbsp;&nbsp;ORDER BY EXTRACT(week FROM col_a);<br><br>I would like to generate the starting date or ending date based on this<br>number.&nbsp;&nbsp;IOW instead of telling the user &quot;week number&quot; which they won't<br>understand I would like to provide either Friday's date for &quot;week
<br>ending&quot; or Monday's date for &quot;week beginning&quot;.<br><br>SELECT &lt;something&gt; AS week_ending,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sum(col_b) AS col_b_total<br>&nbsp;&nbsp; FROM foo<br>&nbsp;&nbsp;WHERE foobar<br>&nbsp;&nbsp;GROUP BY EXTRACT(week FROM col_a)
<br>&nbsp;&nbsp;ORDER BY EXTRACT(week FROM col_a);</blockquote><div><br>Try this.&nbsp; It puts Saturday as the Friday before it and Sunday as the Firday after so if you want Saturday or Sunday to be on different weeks you will need to do a little tweaking but this should get you going.
<br><br>SELECT <br>&nbsp;&nbsp; date_trunc('day', col_a + (interval '1 day' * (5 - extract(dow from col_a)))) AS week_ending,<br>&nbsp;&nbsp; sum(col_b) AS col_b_total<br>FROM foo<br>GROUP BY<br>&nbsp;&nbsp; date_trunc('day', col_a + (interval '1 day' * (5 - extract(dow from col_a)))) 
<br><br><br>&nbsp;</div><br></div>

------=_Part_94177_27005169.1152200971025--