On 2010-02-01, at 14:22 , Lee Hachadoorian wrote:
> The output column data type (day1, day2, etc.) is supposed to match the value data type. I used numeric(10,4) because
that'swhat your original post specified, but the billed_duration column in your most recent post looks like it might be
integer?(Or is it defined as numeric(10,4), but you never enter noninteger values?)
Actually, the query I was running is:
SELECT cust_id as customer, date_trunc(''day'', date) AS day, SUM(billed_duration)/60.0::numeric(10,4) AS minutes
billed_duration is an integer. Make sense?
> What's the output of the category query by itself? I forgot to include ORDER BY 1 at the end of the category query.
(Theorder should match the order of output columns, but I think without it you wouldn't get NULL values, just the
valueswould be in the wrong columns.) I assume day_of_month has only one column, but I would suggest naming it
explicitlyinstead of using *. And is the day_of_month column defined in the same format as date_trunc('day', date)?
Theymust successfully pass an "equals" test to get included in the right crosstab cell. If a category value in the
sourcequery doesn't match any value produced by the category query, I think the crosstab function just throws out that
row,which could lead to a table with the correct structure but all NULLs.
Right, my list of columns weren't equal to the truncated date. Using your suggested query to generate the columns fixed
theproblem!
Now, is there a way to generate the labels? Otherwise I have to adjust the query for th number of days returned.
Such nice output though! Awesome!
Thanks,
A.
--
www.sherman.ca / +1-613-797-6819 / +1-646-233-3400
"When the burning husks of your startups warm the last of your bones, remember I told you so." - Zed