1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| select * from
(select sum(CA) as CA ,sum(Marge) as Marge, avg(N) as N, sum(CA2) as CA2 ,sum(Marge2) as Marge2 , avg(N2) as N2, c.CLI_NOMCLIENT
from
(select sum(BUD_CARVALEUR) as CA ,sum(BUD_MAVALEUR) as Marge ,PER_NUMANNEE as N,CLI_IDCLIENT
from IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE
where BUD_IDPERIODE=PER_IDPERIODE and BUD_IDCLIENT=CLI_IDCLIENT and PER_NUMANNEE=2007
and BUD_IDSITE=SIT_IDSITE and SIT_IDAGE=AGE_IDAGE and AGE_LIBAGENCE like 'Aqui'
group by (PER_NUMANNEE,CLI_IDCLIENT) order by CA desc) r left outer join
(select sum(BUD_CARVALEUR) as CA2 ,sum(BUD_MAVALEUR) as Marge2 ,PER_NUMANNEE as N2 ,CLI_IDCLIENT
from IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE
where BUD_IDPERIODE=PER_IDPERIODE and BUD_IDCLIENT=CLI_IDCLIENT and PER_NUMANNEE=2007-1
and BUD_IDSITE=SIT_IDSITE and SIT_IDAGE=AGE_IDAGE and AGE_LIBAGENCE like 'Aqui'
group by (PER_NUMANNEE,CLI_IDCLIENT)) r2 on r.CLI_IDCLIENT=r2.CLI_IDCLIENT
inner join CLIENT c on r.CLI_IDCLIENT=c.CLI_IDCLIENT
group by (CLI_NOMCLIENT) order by CA desc
)q
where rownum <= 10 |
Partager