title: Select top n percent by year
body: <p>I have a query that I built that brings back net sales YTD up to the most recently completed month. The query unions totals from invoices and credit memos. It works great. I use the query in another tool that sums by cardcode and allows me to do interesting pivots and so forth. Here is that query:</p>
  
  <pre><code>select x.cardcode, 
         x.customer,
         case 
           when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode)
           else
             x.rep
         end as rep, 
         x.city, 
         x.state, 
         x.country,
         case
           when isnumeric(x.total) = 0 then 0
           else x.total
         end as [net total],
         x.docdate
  
  from (
    select t0.cardcode as cardcode, 
           t0.[cardname] as customer, 
           t1.city as city,
           t1.state as state,
           t1.country as country, 
           t4.slpname as rep, 
           sum(t3.linetotal) - t2.discsum as total,
           t2.docdate as [docdate]
  
    from ocrd t0  
         inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
         left outer join oinv t2 on t0.cardcode = t2.cardcode 
         left outer join inv1 t3 on t2.docentry = t3.docentry 
         left outer join oslp t4 on t2.slpcode = t4.slpcode
  
    where t0.[cardtype] = 'C' and 
              t1.adrestype = 'S' 
  
    group by t0.cardcode, t0.cardname, t1.city, t1.state, t1.country, t4.slpname, t2.discsum, t2.docdate
  
    union all
  
    select t0.cardcode as cardcode, 
           t0.cardname as customer, 
           t1.city as city,
           t1.state as state, 
           t1.country as country,
           t4.slpname as rep, 
           -1*(sum(t3.linetotal) - t2.discsum) as total,
           t2.docdate
  
    from ocrd t0  
        inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
        left outer join orin t2 on t0.cardcode = t2.cardcode 
        left outer join rin1 t3 on t2.docentry = t3.docentry 
        left outer join oslp t4 on t2.slpcode = t4.slpcode
  
    where t0.[cardtype] = 'C' and 
            t1.adrestype = 'S' 
  
    group by t0.cardcode, 
             t0.cardname, 
             t1.city,
             t1.state, 
             t1.country,
             t4.slpname, 
             t2.discsum,
             t2.docdate) x 
  
  where (x.docdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01')
          or x.docdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01')
            or x.docdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate())  as varchar(2)) + '/01'))
  
  group by x.cardcode, x.customer, x.rep, x.city, x.state, x.country, x.total, x.docdate
  </code></pre>
  
  <p>Now, I want to modify the query to return the top n, say 20, percent of customer's net total for each year. This is where I am having trouble. I am using SQL Server so first I thought I would try using row_number() over(partition.... but I haven't got it quite right (I know its not right because I can check it against the report I am reverse engineering). Here is my first attempt:</p>
  
  <pre><code>select m.Cardcode, m.Customer, m.Rep, m.City, m.State, m.Country, m.Nettotal as 'Net Total', m.docdate as 'Posting Date'
  from (
    select t.cardcode, t.customer, t.rep, t.city, t.state, t.country, t.nettotal, t.docdate, row_number() over(partition by t.docdate order by t.nettotal desc) as rownum
    from (
        select x.cardcode, 
               x.customer,
               case 
                 when x.rep is null then (select slpname from ocrd inner join oslp on ocrd.slpcode = oslp.slpcode where ocrd.cardcode = x.cardcode)
                 else
                   x.rep
               end as rep, 
               x.city, 
               x.state, 
               x.country,
               case
                 when isnumeric(x.total) = 0 then 0
                 else x.total
               end as nettotal,
               x.docdate
  
        from (
          select t0.cardcode as cardcode, 
                 t0.[cardname] as customer, 
                 t1.city as city,
                 t1.state as state,
                 t1.country as country, 
                 t4.slpname as rep, 
                 sum(t3.linetotal) - t2.discsum as total,
                 t2.docdate as docdate
  
          from ocrd t0  
               inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
               left outer join oinv t2 on t0.cardcode = t2.cardcode 
               left outer join inv1 t3 on t2.docentry = t3.docentry 
               left outer join oslp t4 on t2.slpcode = t4.slpcode
  
          where t0.[cardtype] = 'C' and 
                    t1.adrestype = 'S' 
  
          group by t0.cardcode, 
                   t0.cardname, 
                   t1.city, 
                   t1.state, 
                   t1.country, 
                   t4.slpname, 
                   t2.discsum, 
                   t2.docdate
  
          union all
  
          select t0.cardcode as cardcode, 
                 t0.cardname as customer, 
                 t1.city as city,
                 t1.country as country,
                 t1.state as state, 
                 t4.slpname as rep, 
                 -1*(sum(t3.linetotal) - t2.discsum) as total,
                 t2.docdate
  
          from ocrd t0  
              inner join crd1 t1 on (t0.cardcode = t1.cardcode and t0.shiptodef = t1.address) 
              left outer join orin t2 on t0.cardcode = t2.cardcode 
              left outer join rin1 t3 on t2.docentry = t3.docentry 
              left outer join oslp t4 on t2.slpcode = t4.slpcode
  
          where t0.[cardtype] = 'C' and 
                      t1.adrestype = 'S' 
  
          group by t0.cardcode, 
                   t0.cardname, 
                   t1.city,
                   t1.state, 
                   t1.country,
                   t4.slpname, 
                   t2.discsum,
                   t2.docdate) x 
  
    where (x.docdate between '2008/01/01' and dateadd(day, -1, '2008/' + cast(month(getdate()) as varchar(2)) + '/01')
              or x.docdate between '2009/01/01' and dateadd(day, -1, '2009/' + cast(month(getdate()) as varchar(2)) + '/01')
                or x.docdate between '2010/01/01' and dateadd(day, -1, '2010/' + cast(month(getdate())  as varchar(2)) + '/01'))
  
    group by x.cardcode, 
             x.customer, 
             x.rep, 
             x.city, 
             x.state, 
             x.country, 
             x.total, 
             x.docdate) as t
  ) as m
  
  where rownum &lt;= 20
  </code></pre>
  
  <p>Going down this road is troublesome even if I got it right because it doesn't allow me to get the top n percent, just the top n.</p>
  
  <p>I have yet to try using cross apply or sub selects to achieve the result I desire.</p>
  
  <p>Can someone help me get this right? Also, how it is written is probably not efficient and the hard coded date range selection is not a good solution. I guess there is a lot to improve :)</p>
  
  <p>Your help is appreciated.</p>