October 31, 2004 at 3:16 pm
Hello comunity
When in a query we use for exemple :
select aa,SUM(xx) from tableA where aa='1' group by aa
we must use GROUP BY because this query use an agregate function : SUM
But i have the following query and i have a problem because i need to use pn.vendedor in group by clause because is contain in the case statment.
SELECT cl.no,cl.nome,sum(qtt)as 'Quant',
SUM(ETILIQUIDO) AS 'Valor de Venda',
SUM(Etcusto) as 'Valor de Custo',
'MARGEM'=(case when sum(etcusto)=0 then SUM(ETILIQUIDO)
else
case when sum(etiliquido)=0 then sum(etcusto)*-1
else
case when (sum(etcusto)<0 and sum(etiliquido)<0) then ((1-(sum(etcusto)/sum(etiliquido)))*100)*-1
else
case when (sum(etcusto)>0 and sum(etiliquido)=0) then ((1-(sum(etcusto)/sum(etiliquido)))*100)
else
case when (sum(etcusto)<0 and sum(etiliquido)>0) then ((1-(sum(etcusto)/sum(etiliquido)))*100)
else
(1-(sum(etcusto)/sum(etiliquido)))*100 end end end end end),
'Comissão' =isnull((sum(pn.etiliquido))*
/*GERAL - sem tipo cliente e sem vendedor na ficha do cliente*/
((CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo like ' ' THEN
(SELECT ECOM.COM1 from ecom WHERE ecom.tipocl=0 and ecom.PORVEND=0 AND ECOM.VENDEDOR=0 AND ECOM.QUALTIPO = ' ' AND PN.ECOMISSAO=ecom.ecom)
ELSE
/*Especfica de vendedor*/
CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) THEN
(SELECT ECOM.COM1 from ecom WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR=10 )
else
/*Tipo de Cliente com vendedor na ficha */
CASE WHEN PN.VENDEDOR not IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo not like ' ' then (select ecom.com1 from ecom where ecom.tipocl=1 and ecom.porvend=0 and ecom.qualtipo=pn.tipo)
else
/*Tipo de Cliente - sem vendedor na ficha*/
CASE WHEN PN.VENDEDOR IN(SELECT ECOM.VENDEDOR FROM ECOM WHERE ECOM.VENDEDOR=10) and pn.tipo not like ' ' then (select ecom.com1 from ecom where ecom.tipocl=1 and ecom.vendedor=0 and ecom.porvend=0 and ecom.qualtipo=pn.tipo) end END end END)/100),0)
FROM PN (nolock) inner join cl on pn.no=cl.no
WHERE ORIGEM='FT' and pn.fdata between #1# and #2# and pn.ref <> ' ' /*and pn.ref not like 'NS%' */
GROUP BY cl.no,cl.nome,pn.ecomissao,pn.vendedor,pn.tipo
order by cl.nome asc
suppose i run this query for one Client that have several invoice where pn.vendedor (salesman) is not the same and eventually the values of commissions also, this query return the sum of sails for each pn.vendedor for the same cliente
I need in this case only the totals of sales for this client with the totals of "comissão" (commissions)
In this type of query how do i make my query for not including the field PN.VENDEDOR in the group by clause
Many thanks
Best regards
Luis Santos
October 31, 2004 at 10:09 pm
Luis,
Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
--
Adam Machanic
whoisactive
November 1, 2004 at 8:06 am
If I am understanding this correctly, basically want the Client Number, name, etc., and the various money's (sales and commission amounts) printed out.
Then you want to add the various salespeople attached to these Clients, but without printing out the amounts attributed to the salespeople.
Could you do the first section without pn.Vendordedor? Put that into a temp table, then loop to attach the salespeople to that first set of records?
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply