From: "Pedro B." <pedro.borracha@netcabo.pt>
To: pgsql-sql@postgresql.org
Subject: Re: Given 02-01-2006 to 02-28-2006, output all days.
Date: Mon, 20 Feb 2006 20:30:02 +0000
User-Agent: KMail/1.9.1
References: <144D12D7DD4EC04F99241498BB4EEDCC220D19@nelson.osl.com>
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC220D19@nelson.osl.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200602202030.02701.pedro.borracha@netcabo.pt>

Hello.
I'm having difficulties on my first incursion through generate_series.

The details:

SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
=A0 =A0 =A0 COUNT (o."04-sms") as totalcause98
=A0 =A0 =A0 FROM generate_series(11,19) AS s(d)
=A0LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =3D (DATE_TRUNC('=
month',=20
timestamp'2006-02-01' =A0)::DATE + s.d) and o.cause01=3D98)
=A0GROUP BY s.d ORDER BY 1;


This query (although quite messed up on the date parameters), does exactly=
=20
what i want:=20
"sum column 'cause01=3D98' for a specified date range, including 0's"

    date    | totalcause98
=2D-----------+--------------
 2006-02-12 |            0
 2006-02-13 |            0
 2006-02-14 |            0
 2006-02-15 |            0
 2006-02-16 |           68
 2006-02-17 |          256
 2006-02-18 |          104
 2006-02-19 |           34
 2006-02-20 |           20

I'm using a left join because i really need the =3D0 sums.
The use of substr() is due to the fact the "26-insertTime" on the 'netopia'=
=20
table has a default of 'default (now())::timestamp(2) without time zone'.
So, i can make generate_series work with the left join using the substr.
I was getting ready to optimize this query, when i remembered i also have t=
he=20
need for another column, 'totalcause99', almost the same as this query, but=
=20
with 'cause01=3D99' as condition.

The maximum i was able to do without syntax errors was:

SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
=A0 =A0 =A0 COUNT (o."04-sms") as totalcause98,
=A0 =A0 =A0 COUNT (p."04-sms") as totalcause99
=A0 =A0 =A0 FROM generate_series(11,19) AS s(d)
=A0LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =3D (DATE_TRUNC('=
month',=20
timestamp'2006-02-01' =A0)::DATE + s.d) and o.cause01=3D98)
=A0LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =3D (DATE_TRUNC('=
month',=20
timestamp'2006-02-01' =A0)::DATE + s.d) and p.cause01=3D99)
=A0GROUP BY s.d ORDER BY 1;

Reading this one aloud, i feel the "logic" of what i'm trying to do, but th=
e=20
values of its output are.. scary to say the least, and the sums are exactly=
=20
the same on the 2 columns, and that should never happen with the data i hav=
e=20
on the table.

I'm starting to wonder if this is actually possible to be done on one singl=
e=20
query...
Ideas, anyone?

Sorry for the long email.
Any and all help is deeply appreciated.

Regards,

=2D-=20
\\pb