From lists@turnkey.bz Thu Dec  1 01:03:49 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id B042419F7A76
	for <pgsql-sql@postgresql.org>; Wed, 30 Nov 2011 21:03:49 -0400 (AST)
Received: from dime159.dizinc.com ([66.7.216.77])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <lists@turnkey.bz>) id 1RVv3y-00056P-7Z
	for pgsql-sql@postgresql.org; Thu, 01 Dec 2011 01:03:48 +0000
Received: from [200.32.232.149] (port=53769 helo=[192.168.54.128])
	by dime159.dizinc.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69)
	(envelope-from <lists@turnkey.bz>) id 1RVv3l-0000Xo-3U
	for pgsql-sql@postgresql.org; Wed, 30 Nov 2011 20:03:33 -0500
Message-ID: <4ED6D261.4060202@turnkey.bz>
Date: Wed, 30 Nov 2011 19:03:29 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:8.0) Gecko/20111124 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: running totals with end of month line
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - dime159.dizinc.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - turnkey.bz
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Pg-Spam-Score: 2.9 (++)
X-Archive-Number: 201111/87
X-Sequence-Number: 36060

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi everyone,<br>
    <br>
    I would like to create a query that does a running total for each
    account, but I also want to show a 'phantom' row that gives the end
    of month with the last day of the month as the transaction date.<br>
    <br>
    Here's a sample query:<br>
    <pre><code><span class="syntax0"><span class="syntax8">SELECT</span> n.customer_id, n.order_id, n.order_total, 
    <span class="syntax9">COALESCE</span><span class="syntax18">(</span><span class="syntax9">SUM</span><span class="syntax18">(</span>o.order_total<span class="syntax18">)</span>,<span class="syntax5">0</span><span class="syntax18">)</span> <span class="syntax8">As</span> past_order_total
<span class="syntax8">FROM</span> orders <span class="syntax8">AS</span> n <span class="syntax9">LEFT</span> <span class="syntax8">JOIN</span> orders <span class="syntax8">AS</span> o 
    <span class="syntax8">ON</span> <span class="syntax18">(</span>o.customer_id <span class="syntax18">=</span> n.customer_id 
            <span class="syntax8">AND</span> n.order_datetime <span class="syntax18">&gt;</span> o.order_datetime<span class="syntax18">)</span>
<span class="syntax8">GROUP</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id, n.order_total
<span class="syntax8">ORDER</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id;

--- taken from <a class="moz-txt-link-freetext" href="http://bit.ly/speZzs">http://bit.ly/speZzs</a>

Is there a way to have that 'phantom' row for each account?  I want to result to be ordered by customer_id, account_type.

More details:
In my situation, I have Customers and Grain types.  
I want to generate a result that will show Customer, Grain Type, Daily Avg Bal, Charge Rate, discount, Charge.  
Maybe it's not really possible. I see it a bit hard group it properly, showing only single row per customer per grain.


Many thanks,
Mark
</span></code></pre>
  </body>
</html>



From polobo@yahoo.com Thu Dec  1 01:13:27 2011
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 280E919F7A76
	for <pgsql-sql@postgresql.org>; Wed, 30 Nov 2011 21:13:27 -0400 (AST)
Received: from nm3.bullet.mail.sp2.yahoo.com ([98.139.91.73])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <polobo@yahoo.com>) id 1RVvDI-0005LS-5L
	for pgsql-sql@postgresql.org; Thu, 01 Dec 2011 01:13:26 +0000
Received: from [98.139.91.69] by nm3.bullet.mail.sp2.yahoo.com with NNFMP;
	01 Dec 2011 01:13:10 -0000
Received: from [98.139.91.13] by tm9.bullet.mail.sp2.yahoo.com with NNFMP;
	01 Dec 2011 01:12:21 -0000
Received: from [127.0.0.1] by omp1013.mail.sp2.yahoo.com with NNFMP;
	01 Dec 2011 01:12:21 -0000
X-Yahoo-Newman-Id: 972616.41290.bm@omp1013.mail.sp2.yahoo.com
Received: (qmail 92515 invoked from network); 1 Dec 2011 01:12:21 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=0fE3yUX4xtIMZX8jhm7NBkgwlsZFx7OzR+Poto1/UmOyHK4NjVY9FWho+lo6gH7cSWMV9Qs/ylF+RBqagPCzxKAlQ0AXS0Daf4MS6HBWa3vgVBcc6Hx78fq1FA0ohjurcyJkMaHAZrEmZmkw3LbHu1ZR7kDRYjd61U8zH2dU5Kc=
	;
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1322701940; bh=fvJDtNCuodZ5vCaYFwkU6o254samxmjmNbh5Uu+Q2ho=;
	h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=x5qubRl1B4Hyk0CZc2j4yifL4z4k5Wu5xDSP8d40+HDGlpOSdEVYzPRn3QLlm3RuWjTn3P02NOEJuZ7x1ox6TvvpipI+yn38hwuVbATWWweCDlNzV5pjoEoWj5eQgdQr3kBSstkVtHwZ86tBZ0r4dz2ChzRZBsXVBe+1r2DsuXA=
X-Yahoo-Newman-Property: ymail-3
X-YMail-OSG: WWeKbi0VM1mZNvOHH_gq2jqmAqXnB7O8UJeZQPz2rQfaVlj
	zKynwFOXxsPrnaMJUe3L8jPdFazD5.V1duZgxNftNMNUrBy8NrUK4JjFO2kk
	D986BrlodX3ZJcHd4oH2m2Z3c5xDRGTzmFn8Mrx.LqQ8AIDgtTuGvvYr7xam
	zVLMm8C7YiZWkPXvEboRLfrvKlcBEZre1ORw4pvGmrIvEhFmYj.wuMR6QtuW
	MDKlsBhZyhtnIABXE_KbHovAAJiCz_Ab98nJL6aBn5Euml9S1Cbs_RmIqjoM
	Ocrnz9h2NdCu7i1Onc54swVRjWtwrHlPoIYf6PujbpiXzLr..Jcfz9kDENfT
	Pg908VD_oamb0YIUN4zOk9oyEKyMZurXxlv53plEJmtLKEMzc9KlrWi2dtPI
	VoiU9LYBTorTKiWGCYZQlNwP4mMoNfU_Xq0GWB6hu1FjKiut5g0zl6qqS1Su
	jVKXdQKINy0Ho
X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg--
Received: from [192.12.17.104] (polobo@24.93.23.188 with xymcookie)
	by smtp116-mob.biz.mail.gq1.yahoo.com with SMTP;
	30 Nov 2011 17:12:19 -0800 PST
References: <4ED6D261.4060202@turnkey.bz>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <4ED6D261.4060202@turnkey.bz>
X-Apple-Yahoo-Original-Message-Folder: Inbox
Content-Type: multipart/alternative;
	boundary=Apple-Mail-4-797360029
Content-Transfer-Encoding: 7bit
Message-Id: <E71E635E-4A47-46BF-8899-78D1837D6FBA@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
X-Mailer: iPad Mail (8J2)
From: David Johnston <polobo@yahoo.com>
X-Apple-Yahoo-Replied-Msgid: 1_395346_APOkiGIAAXqxTtbSigoXwhzeao4
Subject: Re: running totals with end of month line
Date: Wed, 30 Nov 2011 20:12:22 -0500
To: "M. D." <lists@turnkey.bz>
X-Pg-Spam-Score: -3.2 (---)
X-Archive-Number: 201111/88
X-Sequence-Number: 36061


--Apple-Mail-4-797360029
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

On Nov 30, 2011, at 20:03, "M. D." <lists@turnkey.bz> wrote:

> Hi everyone,
>=20
> I would like to create a query that does a running total for each account,=
 but I also want to show a 'phantom' row that gives the end of month with th=
e last day of the month as the transaction date.
>=20
> Here's a sample query:
> SELECT n.customer_id, n.order_id, n.order_total,=20
>     COALESCE(SUM(o.order_total),0) As past_order_total
> FROM orders AS n LEFT JOIN orders AS o=20
>     ON (o.customer_id =3D n.customer_id=20
>             AND n.order_datetime > o.order_datetime)
> GROUP BY n.customer_id, n.order_datetime, n.order_id, n.order_total
> ORDER BY n.customer_id, n.order_datetime, n.order_id;
>=20
> --- taken from http://bit.ly/speZzs
>=20
> Is there a way to have that 'phantom' row for each account?  I want to res=
ult to be ordered by customer_id, account_type.
>=20
> More details:
> In my situation, I have Customers and Grain types. =20
> I want to generate a result that will show Customer, Grain Type, Daily Avg=
 Bal, Charge Rate, discount, Charge. =20
> Maybe it's not really possible. I see it a bit hard group it properly, sho=
wing only single row per customer per grain.
>=20
>=20
> Many thanks,
> Mark
You need to write the totaling query and then UNION ALL it with the detail q=
uery.  You probably will want to use WINDOW aggregates as well - assuming yo=
u are on 8.4 or better.

David J.=

--Apple-Mail-4-797360029
Content-Transfer-Encoding: 7bit
Content-Type: text/html;
	charset=utf-8

<html><body bgcolor="#FFFFFF"><div>On Nov 30, 2011, at 20:03, "M. D." &lt;<a href="mailto:lists@turnkey.bz">lists@turnkey.bz</a>&gt; wrote:<br><br></div><div></div><blockquote type="cite"><div>
    Hi everyone,<br>
    <br>
    I would like to create a query that does a running total for each
    account, but I also want to show a 'phantom' row that gives the end
    of month with the last day of the month as the transaction date.<br>
    <br>
    Here's a sample query:<br>
    <pre><code><span class="syntax0"><span class="syntax8">SELECT</span> n.customer_id, n.order_id, n.order_total, 
    <span class="syntax9">COALESCE</span><span class="syntax18">(</span><span class="syntax9">SUM</span><span class="syntax18">(</span>o.order_total<span class="syntax18">)</span>,<span class="syntax5">0</span><span class="syntax18">)</span> <span class="syntax8">As</span> past_order_total
<span class="syntax8">FROM</span> orders <span class="syntax8">AS</span> n <span class="syntax9">LEFT</span> <span class="syntax8">JOIN</span> orders <span class="syntax8">AS</span> o 
    <span class="syntax8">ON</span> <span class="syntax18">(</span>o.customer_id <span class="syntax18">=</span> n.customer_id 
            <span class="syntax8">AND</span> n.order_datetime <span class="syntax18">&gt;</span> o.order_datetime<span class="syntax18">)</span>
<span class="syntax8">GROUP</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id, n.order_total
<span class="syntax8">ORDER</span> <span class="syntax8">BY</span> n.customer_id, n.order_datetime, n.order_id;

--- taken from <a class="moz-txt-link-freetext" href="http://bit.ly/speZzs"><a href="http://bit.ly/speZzs">http://bit.ly/speZzs</a></a>

Is there a way to have that 'phantom' row for each account?  I want to result to be ordered by customer_id, account_type.

More details:
In my situation, I have Customers and Grain types.  
I want to generate a result that will show Customer, Grain Type, Daily Avg Bal, Charge Rate, discount, Charge.  
Maybe it's not really possible. I see it a bit hard group it properly, showing only single row per customer per grain.


Many thanks,
Mark</span></code></pre></div></blockquote><div>You need to write the totaling query and then UNION ALL it with the detail query. &nbsp;You probably will want to use WINDOW aggregates as well - assuming you are on 8.4 or better.</div><div><br></div><div>David J.</div></body></html>
--Apple-Mail-4-797360029--