按年份选择前 n%

我构建了一个查询，可返回截至最近完成的月份的年初至今净销售额。查询联合发票和贷项凭证的总计。效果很好。我在另一个工具中使用查询，该工具通过卡代码求和，并允许我进行有趣的数据透视等。这是该查询：

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

现在，我想修改查询以返回每年客户净总额的前 n 个（例如 20%）。这就是我遇到麻烦的地方。我正在使用 SQL Server，所以首先我想我会尝试使用 row_number() over(partition.... 但我还没有完全正确（我知道它不正确，因为我可以根据我正在逆向工程的报告检查它） ). 这是我的第一次尝试：

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 <= 20

即使我做对了，沿着这条路走下去也是很麻烦的，因为它不允许我获得前 n%，只能获得前 n。

我还没有尝试使用交叉应用或子选择来实现我想要的结果。

有人可以帮我解决这个问题吗？此外，它的编写方式可能效率不高，并且硬编码的日期范围选择也不是一个好的解决方案。我想还有很多需要改进的地方:)

感谢您的帮助。

如果您需要更多的单独百分比（假设为 17%），您可以使用 row_number 和 count：

with cSalesPerYear as (
    select  s.Year, c.Customer,
            RankNo = rank() over (partition by s.Year order by S.Amount desc),
            RowNo = row_number() over (partition by s.Year order by S.Amount desc),
            CountOrders = count() over (partition by s.Year)
    from    dbo.Customers c
    inner join dbo.Sales s
        on  s.CustomerID = c.CustomerID
)
select  *
from    cSalesPerYear
where   RowNo <= @Percentage * CountOrders
     -- RankNo <= @Percentage * CountOrders --<-- "with ties" version