20 % Record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

20 % Record

Author

Topic

mikejanssen
Starting Member

4 Posts

Posted - 2003-12-11 : 03:19:39

Let say i have 500 sales records with four salespersons,
SP1= 100 sales records
SP2= 150 sales records
SP3= 175 sales records
SP4= 75 sales records
i want to view all salesperson sales records, but only 20 % sales records will view each salesperson.

JCamburn
Starting Member

31 Posts

Posted - 2003-12-11 : 03:24:47

Please provide DDL statements and the query you are using to view the records. It will make it easier for us to help you.

mikejanssen
Starting Member

4 Posts

Posted - 2003-12-11 : 03:46:13

This is my DDL

SELECT MAIN.BRANID,
MAIN.DIVID,
MAIN.SALESMAN,
MAIN.CUSTID,
MAIN.CUSTNAME,
MAIN.GS,
MAIN.DISC,
MAIN.AR,
MAIN.PAY
FROM (SELECT HS.BRANCH_ID BRANID,
HS.DIVISION_ID DIVID,
HS.SALESMAN_ID SALESMAN,
HS.CUSTOMER_ID CUSTID,
CUST.CUSTOMER_NAME CUSTNAME,
SUM(HS.GROSS_SALES)GS,
SUM(HS.DISCOUNT)DISC,
NVL(AR.OUTAR,0)AR,
NVL(PAY.PAY,0)PAY
FROM H_SALES HS,
CUSTOMER CUST,
(SELECT AR.BRANCH_ID BRANID,
AR.DIVISION_ID DIVID,
AR.CUSTOMER_ID CUSTID,
SUM(AR.INV_VALUE) OUTAR
FROM AR AR
WHERE AR.BRANCH_ID=:TORG
AND AR.DIVISION_ID=:TDIV
AND AR.PAYMENT_FLAG IS NULL
GROUP BY AR.BRANCH_ID,
AR.DIVISION_ID,
AR.CUSTOMER_ID)AR,
(SELECT RCV.BRANCH_ID BRANID,
RCV.DIVISION_ID DIVID,
RCV.CUSTOMER_ID CUSTID,
SUM(RCV.PAYMENT_VALUE)PAY
FROM RECEIVABLE RCV
WHERE RCV.BRANCH_ID=:TORG
AND RCV.DIVISION_ID=:TDIV
AND RCV.TRANS_MM=:TMONTH
AND RCV.TRANS_YY=:TYEAR
GROUP BY RCV.BRANCH_ID,
RCV.DIVISION_ID,
RCV.CUSTOMER_ID)PAY
WHERE HS.TRANS_MM=:TMONTH
AND HS.TRANS_YY=:TYEAR
AND HS.BRANCH_ID=:TORG
AND HS.DIVISION_ID=:TDIV
AND CUST.BRANCH_ID=:TORG
AND CUST.DIVISION_ID=:TDIV
AND CUST.CUSTOMER_ID=HS.CUSTOMER_ID
AND HS.BRANCH_ID=AR.BRANID(+)
AND HS.DIVISION_ID=AR.DIVID(+)
AND HS.CUSTOMER_ID=AR.CUSTID(+)
AND HS.BRANCH_ID=PAY.BRANID(+)
AND HS.DIVISION_ID=PAY.DIVID(+)
AND HS.CUSTOMER_ID=PAY.CUSTID(+)
GROUP BY HS.BRANCH_ID,
HS.DIVISION_ID,
HS.CUSTOMER_ID,
CUST.CUSTOMER_NAME,
AR.OUTAR,
PAY.PAY,
HS.SALESMAN_ID
ORDER BY SUM(HS.GROSS_SALES) DESC)MAIN
WHERE ROWNUM < (SELECT COUNT(*) * 0.2
FROM (SELECT HS.BRANCH_ID,
HS.DIVISION_ID,
HS.CUSTOMER_ID,
CUST.CUSTOMER_NAME CUSTNAME,
SUM(HS.GROSS_SALES)GS,
SUM(HS.DISCOUNT)DISC
FROM H_SALES HS,
CUSTOMER CUST
WHERE HS.TRANS_MM=:TMONTH
AND HS.TRANS_YY=:TYEAR
AND HS.BRANCH_ID=:TORG
AND HS.DIVISION_ID=:TDIV
AND CUST.BRANCH_ID=:TORG
AND CUST.DIVISION_ID=:TDIV
AND CUST.CUSTOMER_ID=HS.CUSTOMER_ID
GROUP BY HS.BRANCH_ID,
HS.DIVISION_ID,
HS.CUSTOMER_ID,
CUST.CUSTOMER_NAME
ORDER BY SUM(HS.GROSS_SALES) DESC))

From this DDL only view 20 % records from N records. How to view 20 % records each salesperson from N salesperson records ?

Thank's

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-11 : 04:13:07

If there are really only 4 salespersons you can use union (all):

select 20 percent * from t where SP=1
union all
select 20 percent * from t where SP=2
union all
select 20 percent * from t where SP=3
union all
select 20 percent * from t where SP=4

mikejanssen
Starting Member

4 Posts

Posted - 2003-12-11 : 04:17:28

not only 4 salespersons, depend on branch and division.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-11 : 07:39:19

First off, you need a way of ranking these rows to return the top 20 percent. (i.e., 20% based on WHAT?)

Do a search for my username and "Rank" in these forums for tons of examples of calculating a rank for each row in a group. i.e., you might say "top 20% based on sale amount" or "top 20% based on the latest sales".

Then, you need to calculate your total rows per salesperson. Something like:

Select Salesperson, count(*) as Total
from YOurTable

Then, your STARTING point is the total rows per salesperson. Then do an INNER JOIN to the data w/ the rank calculated, and then filter so that rank < (Total * .20)


select 
  Sales.*
from
 (SQL here for total rows per salesperson) Totals
INNER JOIN
 ( SQL here that returns all rows, w/ a "Rank" column) Sales
ON
 Totals.Salesperson = Sales.Salesperson
WHERE 
 Sales.Rank < (Totals.Total * .2)

Not easy stuff, but hopefully this helps. Try it out. But the key is getting the "Rank" of each row per salesperson, and a search here will give you many methods.

- Jeff

mikejanssen
Starting Member

4 Posts

Posted - 2003-12-17 : 23:31:53

Thanks Jeff,
I already try your suggestion but there is a problem with error
"ORA-00933: SQL command not properly ended"
Can you recheck for me maybe i missed something ? if you don't mind.
This is my DDL :
SELECT SALE.SALESMAN,
SALE.GS
FROM
(SELECT A.SALESMAN,COUNT(*) TTL
FROM(SELECT HS.BRANCH_ID BRANID,
HS.DIVISION_ID DIVID,
HS.SALESMAN_ID SALESMAN,
HS.CUSTOMER_ID CUSTID,
CUST.CUSTOMER_NAME CUSTNAME,
SUM(HS.GROSS_SALES)GS,
SUM(HS.DISCOUNT)DISC
FROM H_SALES HS,
CUSTOMER CUST
WHERE HS.TRANS_MM=:TMONTH
AND HS.TRANS_YY=:TYEAR
AND HS.BRANCH_ID=:TORG
AND HS.DIVISION_ID=:TDIV
AND CUST.BRANCH_ID=:TORG
AND CUST.DIVISION_ID=:TDIV
AND CUST.CUSTOMER_ID=HS.CUSTOMER_ID
GROUP BY HS.BRANCH_ID,
HS.DIVISION_ID,
HS.CUSTOMER_ID,
CUST.CUSTOMER_NAME,
HS.SALESMAN_ID
ORDER BY HS.SALESMAN_ID,SUM(HS.GROSS_SALES)DESC)A
GROUP BY A.SALESMAN)TOTALS
INNER JOIN
(SELECT HS.BRANCH_ID BRANID,
HS.DIVISION_ID DIVID,
HS.SALESMAN_ID SALESMAN,
HS.CUSTOMER_ID CUSTID,
CUST.CUSTOMER_NAME CUSTNAME,
SUM(HS.GROSS_SALES)GS,
SUM(HS.DISCOUNT)DISC
FROM H_SALES HS,
CUSTOMER CUST
WHERE HS.TRANS_MM=:TMONTH
AND HS.TRANS_YY=:TYEAR
AND HS.BRANCH_ID=:TORG
AND HS.DIVISION_ID=:TDIV
AND CUST.BRANCH_ID=:TORG
AND CUST.DIVISION_ID=:TDIV
AND CUST.CUSTOMER_ID=HS.CUSTOMER_ID
GROUP BY HS.BRANCH_ID,
HS.DIVISION_ID,
HS.CUSTOMER_ID,
CUST.CUSTOMER_NAME,
HS.SALESMAN_ID
ORDER BY HS.SALESMAN_ID,SUM(HS.GROSS_SALES)DESC)SALE
ON
TOTALS.SALESMAN=SALE.SALESMAN
WHERE
SALE.RANK < (TOTALS.TTL * .2)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-18 : 07:39:34

this is a SQL Server site .... not sure I can really help you with Oracle syntax. Maybe an Oracle forum might be a better idea?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-18 : 09:43:35

Try here:

http://www.dbforums.com/f4/

btw which version of Oracle...8i and 9i have very different join syntax

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources