Re: newbie: A Quickie for some guru out there.

On 15 Sep 2002 23:45:31 -0700, google_at_johnmee.com (John) wrote:

>I'm building report which adds up the number of requested hours per
>month, but the query does not return a zero for months which have no
>record. Thus...
>
>SELECT
> TO_CHAR( trunc( workdate, 'month' ), 'MON-YYYY' ) AS Month,
> ROUND( SUM( requesthours ))
>FROM
> cst_request_table
>GROUP BY
> trunc( workdate, 'month' );
>
>produces...
>
>MONTH REQUESTHOURS
>-------- ------------
>JAN-2002 1
>JUL-2002 1
>AUG-2002 12
>SEP-2002 5
>OCT-2002 15
>
>The desired result is...
>
>MONTH REQUESTHOURS
>-------- ------------
>JAN-2002 1
>FEB-2002 0
>MAR-2002 0
>APR-2002 0
>MAY-2002 0
>JUN-2002 0
>JUL-2002 1
>AUG-2002 12
>SEP-2002 5
>OCT-2002 15
>
>Any neat suggestions?
>thx.

Hi John,

There are several more or less "neat" ways to get around this, but I think the most appropriate/correct one would be to extend your data model with a table called "report_months", which contains all the months you want to report and then join over "cst_request_table" and "report_months" to get the result. Example for illustration :

create table report_months (month char(8)); insert into report_months values ('JAN-2002'); insert into report_months values ('FEB-2002'); .
.
insert into report_months values ('OCT-2002');

And then :

SELECT
   a.month ,
   nvl(b.total,0)
FROM
   report_months a,
   (select TO_CHAR( trunc(workdate, 'month' ), 'MON-YYYY' ) as mon,     sum(request_hours) as total from cst_request_table    group by TO_CHAR( trunc(workdate, 'month' ), 'MON-YYYY' ) ) b   where a.month = b.mon(+)

• Kenneth Koenraadt