| Pedro B. 2006-02-20, 8:24 pm |
| On Monday 20 February 2006 20:39, Mark R. Dingee Pedro wrote:
|> _Pedro,
|>
|> _Would something such as this suffice?
Hello Mark,
It's far superior to what i was doing, serialization wise. Thank you.
However, it still leaves me with the big headache of the left joins with the
"count ... where..."...
Thanks,
\\pb
|>
|> _Mark
|>
|> _create function get_date_range(date,
date) returns setof date as '
|> _DECLARE
|> __ _ cur date;
|> _BEGIN
|> __ _ cur := $1;
|>
|> __ _ while cur <= $2 LOOP
|> __ _ _ _ _return next cur;
|> __ _ _ _ _cur := cur + interval ''1 day'';
|> __ _ end LOOP;
|> __ _ return;
|> _END;' language 'plpgsql';
|>
|> _dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> __get_date_range
|> _----------------
|> __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)
|>
|> _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, COUNT (o."04-sms") as totalcause98
|> _> _ _ _ FROM generate_series(11,1
9) AS s(d)
|> _> _LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> _> (DATE_TRUNC('month',
timestamp'2006-02-01' _)::DATE + s.d) andcreate
|>
|> _function get_date_range(date,
date) returns setof date as '
|> _DECLARE
|> _ _ _cur date;
|> _BEGIN
|> _ _ _cur := $1;
|>
|> _ _ _while cur <= $2 LOOP
|> _ _ _ _ _ return next cur;
|> _ _ _ _ _ cur := cur + interval ''1 day'';
|> _ _ _end LOOP;
|> _ _ _return;
|> _END;' language 'plpgsql';
|>
|> _dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> _ get_date_range
|> _----------------
|> _ 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=98)
|> _> _GROUP BY s.d ORDER BY 1;
|> _>
|> _>
|> _> This query (although quite messed up on the date parameters), does
|> _> exactly what i want:
|> _> "sum column 'cause01=98' 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 =0 sums.
|> _> The use of substr() is due to the fact the "26-insertTime" on the
|> _> 'netopia' 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 query, but with 'cause01=99' 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, COUNT (o."04-sms") as totalcause98,
|> _> _ _ _ COUNT (p."04-sms") as totalcause99
|> _> _ _ _ FROM generate_series(11,1
9) AS s(d)
|> _> _LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> _> (DATE_TRUNC('month',
timestamp'2006-02-01' _)::DATE + s.d) and
|> _> o.cause01=98)
|> _> _LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =
|> _> (DATE_TRUNC('month',
timestamp'2006-02-01' _)::DATE + s.d) and
|> _> p.cause01=99)
|> _> _GROUP 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,
|>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|