Top 5% of Sales for each salesman RRS feed

  • Question

  • Above says it. I'm trying to select a random 5% of sales for each salesman for the last day serviced. I've got it all down, but I can't get it down to salesman. I'm using a basic select:

    name, address, order, order_id, etc where order_id in (select top 5 percent order_id from MYSERVER group by salesman, order_ID order by newid())

    The actual statement's much longer and has more where statements, but the pertinent info is above. All I can manage to do is create very long-winded ways to pick a random 5% of all sales.
    Monday, August 28, 2006 12:01 PM

All replies

  • What about:

    Select * From Orders O

    WHERE Exists


    Select TOP 5 PERCENT OrderId FROm Orders O

    Where O.Empid = OD.Empid


    HTH, Jens Suessmeyer.


    Monday, August 28, 2006 2:13 PM
  • That seems to return all records
    Monday, August 28, 2006 4:32 PM
  • Hmhh, you are definitely right.
    Monday, August 28, 2006 4:50 PM
  • That works. Thanks. I'm not following the logic though. Can you explain how comparing sales_id's gives me the proper grouping?
    Wednesday, August 30, 2006 1:37 PM
  • I spoke too soon. As I go back and check it out, it's not doing what I need it to. It seems to be selecting the right number of orders, but the salesman are random. I'm trying to get it to select 5% of each individual salesman.
    Monday, September 25, 2006 12:27 PM
  • -- ------------------------------------------------------------
    --   A select statement for selecting the most recent 5% of
    --   orders on record for each salesman (employeeId).
    --   1.  Note the DESC tag on the order ID for the rank; this
    --       is what causes the most recent orders to be targeted
    --   2.  Also take note of the ROUND function as part of the
    --       "OrderFilter" subquery.  This rounding might cause
    --       no rows to be returned for a salesman that has only
    --       one order; if the real real intent is that "no less
    --       than 5%" of the orders be displayed, this should be
    --       changed to the "ceiling" function instead.
    -- ------------------------------------------------------------

    select orderList.employeeId,
     from (
            select employeeId,
                   rank () over
                        ( partition by employeeId
                          order by orderId desc
                        ) as orderSeq
              from orders
          ) OrderList
     inner join
          ( select employeeId,
                   round (0.05 * count(*), 0) as [targetCount]
      --             ceiling (0.05 * count(*)) as [targetCount]
              from orders
            group by employeeId
          ) OrderFilter
         on orderList.employeeId = orderFilter.employeeId
        and orderList.orderSeq <= orderFilter.targetCount

    -- ------------------------------------------------------------
    --   This version will work for SQL Server 2000
    -- ------------------------------------------------------------
    select OrderList.employeeId,
     from ( select a.employeeId,
                   count(*) as [orderSeq]
              from orders a
             inner join orders b
                on a.employeeId = b.employeeId
               and a.orderId <= b.orderId
            group by a.employeeId,
          ) OrderList
    inner join
          ( select employeeId,
                   round (0.05 * count(*), 0) as [targetCount]
    --               ceiling (0.05 * count(*)) as [targetCount]
              from orders
            group by employeeId
          ) OrderFilter
         on OrderList.employeeId = OrderFilter.employeeId
        and OrderList.orderSeq <= orderFilter.targetCount
    order by OrderList.employeeId,

    Tuesday, September 26, 2006 6:47 PM