none
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.

    ---
    http://www.sqlserver2005.de
    ---

    Monday, August 28, 2006 2:13 PM
    Moderator
  • That seems to return all records
    Monday, August 28, 2006 4:32 PM
  • Using the Northwind sample database to demonstrate,
    
    with SQL Server 2005:
    
    select O.*
    from Northwind..Employees as E
    cross apply (
       select top (5) percent *
       from Northwind..Orders as O
       where O.EmployeeID = E.EmployeeID
       order by newid()
    ) as O
    
    
    With SQL Server 2000:
    
    select *
    from Northwind..Orders as O1
    where OrderID in (
       select top 5 percent OrderID
       from Northwind..Orders as O2
       where O2.EmployeeID = O1.EmployeeID
       order by newid()
    )
    
    
    Steve Kass
    Drew University
    www.stevekass.com
    
    
    aragon127@discussions.microsoft.com wrote:
    
    > 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 4:47 PM
  • Hmhh, you are definitely right.
    Monday, August 28, 2006 4:50 PM
    Moderator
  • 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,
           orderList.orderSeq,
           orderFilter.targetCount,
           orderList.orderId
     from (
            select employeeId,
                   orderId,
                   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,
           OrderList.orderId,
           OrderList.orderSeq,
           OrderFilter.targetCount
     from ( select a.employeeId,
                   a.OrderId,
                   count(*) as [orderSeq]
              from orders a
             inner join orders b
                on a.employeeId = b.employeeId
               and a.orderId <= b.orderId
            group by a.employeeId,
                     a.OrderId
          ) 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,
             OrderList.orderSeq,
             OrderList.OrderId
    */

    Tuesday, September 26, 2006 6:47 PM
    Moderator