20 % records
-
Dear friends,
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.
-
This was removed by the editor as SPAM
-
Does the table have a unique id? If so then
select a.*
from tablea a
where a.[uniqueid] in
(select top 20 percent [uniqueid]
from tablea
where [salesperson] = a.[salesperson]
order by [somecol])
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
This my DDL, i hope you can help me !
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
-
Create a temp table #temp with the following columns
ROWID int IDENTITY(1,1), BRANID, DIVID, SALESMAN, CUSTID, CUSTNAME, GS, DISC
INSERT INTO #temp
(BRANID, DIVID, SALESMAN, CUSTID, CUSTNAME, GS, DISC)
SELECTHS.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....
GROUP BY...
ORDER BY HS.SALESMAN_ID,SUM(HS.GROSS_SALES) DESC
SELECT a.BRANID, a.DIVID, a.SALESMAN, a.CUSTID, a.CUSTNAME, a.GS, a.DISC
FROM #temp a
INNER JOIN (SELECT SALESMAN,MIN(ROWID) as MINID FROM #temp GROUP BY SALESMAN) b
ON b.SALESMAN = a.SALESMAN
INNER JOIN (SELECT SALESMAN,MAX(ROWID) as MINID FROM #temp GROUP BY SALESMAN) c
ON c.SALESMAN = a.SALESMAN
WHERE a.ROWID <= (b.MINID + ((c.MAXID - b.MINID + 1) * 0.2))
Edited by - davidburrows on 12/18/2003 05:50:28 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David,
I think that you're suggest to me is not in oracle syntax, can you tell me in oracle, please ?
-
Thanks David,
I think that you're suggest to me is not in oracle syntax, can you tell me in oracle, please ?
-
Ah! Thought so when I looked at the code as it did not look like traditional sql server.
Unfortunately I have not used Oracle and cannot help you, maybe someone else on the forum can.
Sorry I cannot be of more help
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply