I have a query that pulls totals for the month and from there I am
building a crosstab to show all months. My dilemma is that sometimes
there is no data for a month and the crosstab becomes skewed. I made a
table with all the 12 months in it and joined to the query in my view to
get all the months for any year there was sales to show in the query
results, surely there is a better way? But when spanning different years
like in the query below, that does not work as I only get the 12 months
of the years where sales occurred in my query leaving out 2005 since
this user had no sales in 2005.
primepay=# select * from view_pick1 WHERE rep = 'aespinal' and nmonth >= '12/01/2005' and nmonth <= '11/30/2006' ORDER
BY1;
rep | nmonth | units | revenue
----------+------------+-------+---------
aespinal | 2006-01-01 | |
aespinal | 2006-02-01 | |
aespinal | 2006-03-01 | |
aespinal | 2006-04-01 | |
aespinal | 2006-05-01 | 4 |
aespinal | 2006-06-01 | 3 |
aespinal | 2006-07-01 | |
aespinal | 2006-08-01 | |
aespinal | 2006-09-01 | |
aespinal | 2006-10-01 | |
aespinal | 2006-11-01 | |
(11 rows)
I need to make sure there is always 12 rows with all months for each
type. Is there any kind of query I could make to build a list of all
months whether they had sales in that year or not? Right now, this query
below is what I'm using to get all the months of any year there were
sales. The view_pick1_data view is the query where the totals are built.
The view_pick1 shown in the above query takes all the months in the
result of the query below and joins the view_pick1_data. I know there
must be a better way, I'm struggling to figure it out.
SELECT DISTINCT view_pick1_data.rep, view_pick1_data.nyear, months."month"
FROM view_pick1_data, months
ORDER BY view_pick1_data.rep, view_pick1_data.nyear, months."month";
Thanks for any help in advance!
--
Robert
