Amarnath wrote:
> There are so many ways you can do, you can create a table with all 12 months
> in a column and do a outer join with the other table to get even 0 values,
> probabily you need to used isnull to make it 0, because it returns null for
> values not existing.
> Or create a temp table with all 12 months values and insert all the values
> you can put this in a stored proc.
> These are some of the way
>
> Amarnath
>
> "Peter" wrote:
>
> > Hello,
> >
> > I've created a report using SQL Server reporting services that looks
> > approximately like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > This is my query behind this report (Oracle):
> >
> > SELECT SUM(DISC) AS DISC, IMPACT, ADDDATE, YEAR, MONTH
> > FROM RPT_DRS_BY_MONTH_VU
> > WHERE (TO_DATE(ADDDATE, 'MM/YYYY') BETWEEN TO_DATE(:pm_date1,
> > 'MM/YYYY') AND TO_DATE(:pm_date2, 'MM/YYYY')) AND (TO_CHAR(TE_DM_S_ID)
> > LIKE :pm_sid) AND (TO_CHAR(TE_DM_L_ID) LIKE
> > :pm_lid)
> > GROUP BY IMPACT, ADDDATE, YEAR, MONTH
> > ORDER BY YEAR, MONTH
> >
> > This report doesn't have any data for 03/2006, 06-08/2006. I need to
> > add these rows with the values 0, so the report would look like this:
> >
> > MONTH Non-Operational DRs Operational DRs Monthly Total
> > 01/2006 6 32 38
> > 02/2006 18 25 43
> > 03/2006 0 0 0
> > 04/2006 19 41 60
> > 05/2006 6 27 33
> > 06/2006 0 0 0
> > 07/2006 0 0 0
> > 08/2006 0 0 0
> > 09/2006 14 14
> > 10/2006 11 5 16
> > 11/2006 2 2
> > TOTAL 69 153 222
> >
> > What would be the best way to do this?
> >
> > I think I should use a UNION query. I know couple ways to do this (like
> > creating a table with all values then selecting missing values and
> > adding them using UNION), but they are bulky and not very efficient.
> >
> > What would be the best way to achieve this?
> >
> > I would appreciate your help.
> >
> > Thank you,
> >
> > Peter
> >
> >