Re: SQL help please, it shouldn't be this hard.

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 11 Jan 2000 15:09:55 +0100
Message-ID: <85fdjk$6i7$1@news4.isdnet.net>

Here's an idea of what you can do:

drop table emp cascade constraints;
create table emp

   (emp_id number primary key,
    name varchar2(30),
    manager number references emp);

insert into emp values (1,'Jughead',  null);
insert into emp values (2,'Janice',   1);
insert into emp values (3,'Todd',     1);
insert into emp values (4,'Monica',   3);
insert into emp values (5,'Peter',    3);
insert into emp values (6,'Courteney',5);
insert into emp values (7,'Mary',     3);
insert into emp values (8,'Bob',      7);
insert into emp values (9,'Shirley',  1);
insert into emp values (10,'Jennifer',9);

commit;
drop table sales_summary;
create table sales_summary

   (sales_id number references emp,
    tot_sales number);

insert into sales_summary values (1,0);
insert into sales_summary values (2,2000);
insert into sales_summary values (3,1000);
insert into sales_summary values (4,1000);
insert into sales_summary values (5,3000);
insert into sales_summary values (6,4000);
insert into sales_summary values (7,2000);
insert into sales_summary values (8,1000);
insert into sales_summary values (9,0);
insert into sales_summary values (10,1000);

commit;

column num noprint
select v1.num, v1.lvl "Level",

       substr(lpad(' ',2*(v1.lvl-1))||v1.name,1,30) "Name",
       sum(c.tot_sales) "Total Sales"
from (select level lvl, emp_id, name, rownum num
      from emp a
      connect by prior emp_id=manager
      start with manager is null) v1,
     sales_summary c, sales_summary e
where c.sales_id in (select emp_id from emp d
                     connect by prior emp_id=manager
                     start with d.emp_id=e.sales_id)

and exists (select 1 from emp b where b.manager=e.sales_id) and v1.emp_id = e.sales_id
group by v1.num, v1.lvl, v1.name;

     Level Name                           Total Sales
---------- ------------------------------ -----------
         1 Jughead                              15000
         2   Todd                               12000
         3     Peter                             7000
         3     Mary                              3000
         2   Shirley                             1000

5 rows selected.

This is far from perfect: as it is, it is necessary to have a row in table sales_summary for each manager, even if he doesn't sale anything. I think it is not too hard to enhance that but i have no time to go further, sorry.

--
Have a nice day
Michel

zpayne <jakefound_at_hotmail.com> a écrit dans le message : 856an0$cnb$1_at_bgtnsc01.worldnet.att.net...
> Ok here is an example.
> In this example Jughead, Todd, Peter, and Mary are managers, and their sales
> include those reporting to them.
>
> This is how the report needs to look.
>
> Level Name Total Sales
> 3 Jughead 15000
> 2 Todd 12000
> 1 Peter 7000
> 1 Mary 3000
>
> In this example salespeople reporting to Mary, and Mary's sales combine to
> 3000 in sales.
> Salespeople reporting to Peter, and Peter's sales combine to 3000 in sales.
> Todd's Sales along with his salespeople, and Peter and Mary combine to
> 12000.
> Jughead has non management salespeople reporting to him, along with Todd,
> Peter, and Mary for 15000.
>
> The tables are as follows.
>
> EMP
> ----------------- -------------
> EMP_ID number
> Manager number foreighn key to EMP(emp_id)
>
>
> SALES_SUMMARY
> ---------------- -----------------
> Sales_id foreign key to EMP(emp_id)
> tot_sales number
>
>
> I hope this helps.
>
> TIA
>
> Doug O'Leary <dkoleary_at_mediaone.net> wrote in message
> news:MPG.12e064894bb1796498970c_at_nntp.ce.mediaone.net...
> > In article <853th9$m9h$3_at_bgtnsc02.worldnet.att.net>,
> > jakefound_at_hotmail.com says...
> > > I'm trying to write a query that for some reason seems harder than it
> should
> > > be.
> >
> > It might help if you posted a short example of what you wanted the report
> > to look like and your table definitions. Don't include live data, just
> > examples...
> >
> > Doug
> >
> > --
> > ==============
> > Douglas K. O'Leary
> > Senior System Admin
> > dkoleary_at_mediaone.net
> > ==============
>
>
Received on Tue Jan 11 2000 - 08:09:55 CST