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

Pedro,

Would something such as this suffice?

Mark=20

create function get_date_range(date, date) returns setof date as '
DECLARE
=A0 =A0 cur date;
BEGIN
=A0 =A0 cur :=3D $1;

=A0 =A0 while cur <=3D $2 LOOP
=A0 =A0 =A0 =A0 =A0return next cur;
=A0 =A0 =A0 =A0 =A0cur :=3D cur + interval ''1 day'';
=A0 =A0 end LOOP;
=A0 =A0 return;
END;' language 'plpgsql';

dev=3D# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
=A0get_date_range
=2D---------------
=A02006-02-01
=A02006-02-02
=A02006-02-03
=A02006-02-04
=A02006-02-05
=A02006-02-06
=A02006-02-07
=A02006-02-08
=A02006-02-09
=A02006-02-10
=A02006-02-11
=A02006-02-12
=A02006-02-13
=A02006-02-14
=A02006-02-15
=A02006-02-16
=A02006-02-17
=A02006-02-18
=A02006-02-19
=A02006-02-20
=A02006-02-21
=A02006-02-22
=A02006-02-23
=A02006-02-24
=A02006-02-25
=A02006-02-26
=A02006-02-27
=A02006-02-28
(28 rows)

On Monday 20 February 2006 15:30, Pedro B. wrote:
> 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', timestamp'2006-02-01' =A0)::DATE + s.d) andcreate=20
function get_date_range(date, date) returns setof date as '
DECLARE
    cur date;
BEGIN
    cur :=3D $1;

    while cur <=3D $2 LOOP
         return next cur;
         cur :=3D cur + interval ''1 day'';
    end LOOP;
    return;
END;' language 'plpgsql';

dev=3D# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
 get_date_range
=2D---------------
 2006-02-01
 2006-02-02
 2006-02-03
 2006-02-04
 2006-02-05
 2006-02-06
 2006-02-07
 2006-02-08
 2006-02-09
 2006-02-10
 2006-02-11
 2006-02-12
 2006-02-13
 2006-02-14
 2006-02-15
 2006-02-16
 2006-02-17
 2006-02-18
 2006-02-19
 2006-02-20
 2006-02-21
 2006-02-22
 2006-02-23
 2006-02-24
 2006-02-25
 2006-02-26
 2006-02-27
 2006-02-28
(28 rows)
> o.cause01=3D98)
> =A0GROUP BY s.d ORDER BY 1;
>
>
> This query (although quite messed up on the date parameters), does exactly
> what i want:
> "sum column 'cause01=3D98' for a specified date range, including 0's"
>
>     date    | totalcause98
> ------------+--------------
>  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 'netopi=
a'
> 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
> the need for another column, 'totalcause99', almost the same as this quer=
y,
> but 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', 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', 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
> the values of its output are.. scary to say the least, and the sums are
> exactly the same on the 2 columns, and that should never happen with the
> data i have on the table.
>
> I'm starting to wonder if this is actually possible to be done on one
> single query...
> Ideas, anyone?
>
> Sorry for the long email.
> Any and all help is deeply appreciated.
>
> Regards,