Results 1 to 3 of 3

Thread: 20% Limit View

  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Question Unanswered: 20% Limit View

    Hi Friends,

    I have some problem, maybe someone in this forum can solve it.

    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 based on top sale amount will view each salesperson.

    I can view only one by one salesperson and i don't know to view all salesperson. This my syntax :

    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

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i want to view all salesperson sales records,
    >but only 20 % sales records based on top sale amount
    >will view each salesperson.
    HUH?
    "top sale amount"? Maximum across all sales-critters?
    OR maximum per each sales-critter?

    What does "will view each salesperson" mean in English?

    If/when you can clearly state the problem, you might get some useful suggestions.

    P.S.
    Rhetorical questions...
    Why?
    For which school, class & instructor is this assignment?

  3. #3
    Join Date
    Dec 2003
    Posts
    2

    Question

    My syntax result

    Branid Divid Salesman Custid GS Disc
    ------- ----- ----------- ------- ----------- --------
    01 01 John 2022 1,025 102.5
    01 01 John 2015 915 91.5
    01 01 John 2036 850 85.0
    Until 100 records

    01 01 Adam 2045 2,226 222.6
    01 01 Adam 2071 1,524 152.4
    Until 150 records

    01 01 Smith 2039 1,315 131.5
    01 01 Smith 2085 750 75.0
    Until 175 records

    01 01 Kevin 3156 2,423 242.3
    01 01 Kevin 3189 1,450 145.0
    Until 75 records

    I want to view only 20 % records of salesman :

    Branid Divid Salesman Custid GS Disc
    ------- ----- ----------- ------- ----------- --------
    01 01 John 2022 1,025 102.5
    01 01 John 2015 915 91.5
    01 01 John 2036 850 85.0
    Until 20 records

    01 01 Adam 2045 2,226 222.6
    01 01 Adam 2071 1,524 152.4
    Until 30 records

    01 01 Smith 2039 1,315 131.5
    01 01 Smith 2085 750 75.0
    Until 35 records

    01 01 Kevin 3156 2,423 242.3
    01 01 Kevin 3189 1,450 145.0
    Until 15 records

    what is a syntax for the result above ?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •