Home » SQL & PL/SQL » SQL & PL/SQL » Totalizing a column (merged)
| Totalizing a column (merged) [message #404406] |
Thu, 21 May 2009 12:58  |
leon945
Messages: 3
Registered: May 2009
Location: Monterrey, Mexico
|
Junior Member |
|
|
Hi,
I have this query..
select telefonoorigen as numerotelefonico,rc.idsitio,t.descripcion,sf.descripcion as nomSitio,rh.idtipollamada,count(*) llamadas,SUM(duracionmins) minutos,
(select case when sum(rcc.cantidad)>0 then
sum(rcc.cantidad) else 0 end
from rcantidadcontratado rcc
where rcc.idtipollamada=rh.idtipollamada and
rcc.IDSITIO= rc.idsitio and
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND
(rcc.FECHAAPLICA <=200904) AND (rcc.ONNET = 0) ) as incluidas,case when
((select case when
sum(cantidad)>0 then
sum(cantidad) else 0 end
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and
rcc.IDSITIO=rc.idsitio and
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND
(rcc.FECHAAPLICA <=200904)) - count(*))<0 then
((select case when sum(cantidad)>0 then
sum(cantidad) else 0 end
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and
rcc.IDSITIO=rc.idsitio and
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND
(rcc.FECHAAPLICA <=200904) ) - count(*)) *-1 else 0 end as noincluidas,
case when ((select case when
sum(cantidad)>0 then
sum(cantidad) else 0 end
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and
rcc.IDSITIO=rc.idsitio and
(rcc.FECHACIERRE IS NULL OR Rcc.FECHACIERRE >=200904) AND
(rcc.FECHAAPLICA <=200904)) - count(*))<0 then
((select case when sum(cantidad)>0 then sum(cantidad) else 0 end
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and
rcc.IDSITIO=rc.idsitio and
(rcc.FECHACIERRE IS NULL OR Rcc.FECHACIERRE >=200904) AND
(rcc.FECHAAPLICA <=200904)) - count(*))*-1 else 0 end * (select importeactual from tipollamada where idtipollamada=rh.idtipollamada) as total,
(select importeactual from tipollamada where idtipollamada=rh.idtipollamada) as tarifa
from rhistorialdetallellamadas rh
INNER JOIN tipollamada t ON(rh.idtipollamada=t.idtipollamada)
INNER JOIN rclientenumerotelefonico rc ON(TO_CHAR(rc.numerotelefonico)=rh.telefonoorigen)
INNER JOIN cliente c ON(c.idcliente=rc.idcliente and c.idtipocliente=2)
INNER JOIN sitiofactura sf on(sf.idsitiofactura=rc.idsitio)
where c.idcliente=8630 and
TO_NUMBER(TO_CHAR(TO_DATE(fechallamada,'DD/MM/YYYY'),'MM'))=04 and
TO_NUMBER(TO_CHAR(TO_DATE(fechallamada,'DD/MM/YYYY'),'YYYY'))=2009 AND
TO_DATE(rh.FECHALLAMADA,'DD/MM/YYYY')>=TO_DATE(C.FECHAACTIVACION,'DD/MM/YYYY')
group by telefonoorigen,rc.idsitio,sf.descripcion,rh.idtipollamada,t.descripcion
order by telefonoorigen,rc.idsitio,idtipollamada,llamadas desc;
Sorry if it's a mess..
Here a screenshot of the results, and what i dont know how to do..
I want the "TOTAL" column to be totalized every time the "NUMEROTELEFONICO" changes..
Could anyone help me with this please?
I'd appreciate it.. thanks in advanced
-
Attachment: datos.jpg
(Size: 122.12KB, Downloaded 419 times)
|
|
|
|
| Re: Totalizing a column [message #404408 is a reply to message #404406] |
Thu, 21 May 2009 13:08  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SELECT telefonoorigen AS numerotelefonico,
rc.idsitio,
t.descripcion,
sf.descripcion AS nomsitio,
rh.idtipollamada,
Count(* ) llamadas,
Sum(duracionmins) minutos,
(SELECT CASE
WHEN Sum(rcc.cantidad) > 0 THEN Sum(rcc.cantidad)
ELSE 0
END
FROM rcantidadcontratado rcc
WHERE rcc.idtipollamada = rh.idtipollamada
AND rcc.idsitio = rc.idsitio
AND (rcc.fechacierre IS NULL
OR rcc.fechacierre >= 200904)
AND (rcc.fechaaplica <= 200904)
AND (rcc.onnet = 0)) AS incluidas,
CASE
WHEN ((SELECT CASE
WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
ELSE 0
END
FROM rcantidadcontratado rcc
WHERE rcc.idtipollamada = rh.idtipollamada
AND rcc.idsitio = rc.idsitio
AND (rcc.fechacierre IS NULL
OR rcc.fechacierre >= 200904)
AND (rcc.fechaaplica <= 200904)) - Count(* )) < 0 THEN ((SELECT CASE
WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
ELSE 0
END
FROM rcantidadcontratado rcc
WHERE rcc.idtipollamada = rh.idtipollamada
AND rcc.idsitio = rc.idsitio
AND (rcc.fechacierre IS NULL
OR rcc.fechacierre >= 200904)
AND (rcc.fechaaplica <= 200904)) - Count(* )) *- 1
ELSE 0
END AS noincluidas,
CASE
WHEN ((SELECT CASE
WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
ELSE 0
END
FROM rcantidadcontratado rcc
WHERE rcc.idtipollamada = rh.idtipollamada
AND rcc.idsitio = rc.idsitio
AND (rcc.fechacierre IS NULL
OR rcc.fechacierre >= 200904)
AND (rcc.fechaaplica <= 200904)) - Count(* )) < 0 THEN ((SELECT CASE
WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
ELSE 0
END
FROM rcantidadcontratado rcc
WHERE rcc.idtipollamada = rh.idtipollamada
AND rcc.idsitio = rc.idsitio
AND (rcc.fechacierre IS NULL
OR rcc.fechacierre >= 200904)
AND (rcc.fechaaplica <= 200904)) - Count(* )) *- 1
ELSE 0
END * (SELECT importeactual
FROM tipollamada
WHERE idtipollamada = rh.idtipollamada) AS total,
(SELECT importeactual
FROM tipollamada
WHERE idtipollamada = rh.idtipollamada) AS tarifa
FROM rhistorialdetallellamadas rh
INNER JOIN tipollamada t
ON (rh.idtipollamada = t.idtipollamada)
INNER JOIN rclientenumerotelefonico rc
ON (To_char(rc.numerotelefonico) = rh.telefonoorigen)
INNER JOIN cliente c
ON (c.idcliente = rc.idcliente
AND c.idtipocliente = 2)
INNER JOIN sitiofactura sf
ON (sf.idsitiofactura = rc.idsitio)
WHERE c.idcliente = 8630
AND To_number(To_char(To_date(fechallamada,'DD/MM/YYYY'),'MM')) = 04
AND To_number(To_char(To_date(fechallamada,'DD/MM/YYYY'),'YYYY')) = 2009
AND To_date(rh.fechallamada,'DD/MM/YYYY') >= To_date(c.fechaactivacion,'DD/MM/YYYY')
GROUP BY telefonoorigen,
rc.idsitio,
sf.descripcion,
rh.idtipollamada,
t.descripcion
ORDER BY telefonoorigen,
rc.idsitio,
idtipollamada,
llamadas DESC;
|
|
|
|
|
|
| Re: Totalizing a column [message #404416 is a reply to message #404410] |
Thu, 21 May 2009 13:52 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
|
If you want to do it yourself next time, the Posting Guidelines (link in top-part of the forum page) contains hints & tips on how to format your code.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 19:13:35 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
