July 7, 2004 at 2:27 pm
We have the following query:
select sum(in.p1) from (
SELECT sr.salesperson, count(distinct act.custaccno) p1
FROM sales_report sr ,activations act
WHERE sr.dtvcustno = act.custaccno AND (act.dateactivation >= '5/1/2004')
AND (act.dateactivation <= '5/31/2004')
group by sr.salesperson
) in
It is supposed to count the total number of accounts. The query inside gets the number of accounts (act.custaccno) handled by each salesperson(sr.salesperson). If we don't put the salesperson in the select (then no group by is needed) the total is different.
We have checked a lot of times our tables and dtvcustno and custaccno are not duplicated in the correspondent table.
What's the problem!!!!!!!
July 7, 2004 at 11:13 pm
My first guess is that when you're grouping by salesperson, your distinct number is includes custaccno's that are also assigned to another salesperson. If you have 25 customers and they have 2 salespeople assigned to them, the distinct will show only 25 customers, but if you group by salesperson, they will show 26 customers counting for the customer assigned to two salespeople.
Another guess would be to count the distinct sr.dtvcustno instead of act.custoaccno since they theoretically should be the same.
July 8, 2004 at 3:18 am
To check bobsterboy's hypothesis, which I think is correct.
( multiple salesperson assigned to 1 custaccno ).
You could try the following sql:
SELECT count(distinct sr.salesperson) as no_of_salesp, act.custaccno
FROM sales_report sr ,activations act
WHERE sr.dtvcustno = act.custaccno AND (act.dateactivation >= '5/1/2004')
AND (act.dateactivation <= '5/31/2004')
group by act.custaccno
having count(distinct sr.salesperson) > 1
/rockmoose
You must unlearn what You have learnt
July 8, 2004 at 4:50 am
One of the following queries should give you the cause of the problem:
SELECT act.custaccno, COUNT(DISTINCT sr.salesperson)
FROM sales_report sr INNER JOIN activations act ON sr.dtvcustno = act.custaccno
WHERE act.dateactivation BETWEEN '5/1/2004' AND '5/31/2004'
GROUP BY act.custaccno HAVING COUNT(DISTINCT sr.salesperson)>1
SELECT act.custaccno FROM activations act
WHERE act.custaccno NOT IN (SELECT sr.dtvcustno FROM sales_report sr)
AND act.dateactivation BETWEEN '5/1/2004' AND '5/31/2004'
Razvan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply