sql - 如何在 T-SQL 中计算 ABCIndicator

Question

我有一个销售数据表，例如:

SELECT ItemCode, ItemDesc, TotalYearlySales, ShareOfBusiness, ABCIndicator
FROM Sales
WHERE Yir = Year(getdate())
AND Manth = Month(getdate())
ORDER BY TotalYearlySales DESC

ShareOfBusiness 计算为项目的 (TotalYearlySales/SUM(TotalYearlySales))*100
对于总和为 SUM(ShareOfBusiness) 80% 的项目列表，ABCIndicator 是 A。 B 代表接下来的 15%，C 代表最后的 5%。

如何获得每个项目的 ABCIndicator？

样本数据:

ItemCode ItemDesc      TotalYearlySales ShareOfBusiness ABCIndicator
1234     Yellow Flute  3000             .36             A
1235     Brown Violin  2000             .24             A
1236     Silver Flute  1800             .21             A
1236     Pink Drums    1500             .18             B

Answer 1

您可以在子查询中计算总和和运行总和。运行总和是该行和总和较高的行的总销售额。

select
    ItemCode
,   ItemDesc
,   TotalYearlySales
,   TotalYearlySales / SalesSum as Share
,   case when RunningSum > 0.2 * SalesSum then 'A'
         when RunningSum > 0.05 * SalesSum then 'B'
         else 'C'
    end as ABCIndicator    
from (
    select
        ItemCode
    ,   ItemDesc
    ,   TotalYearlySales
    ,   (
        select sum(TotalYearlySales) 
        from @Sales t1
        where Yir = Year(getdate())
        ) as SalesSum
    ,   (
        select sum(TotalYearlySales) 
        from @Sales t2
        where Yir = Year(getdate()) 
              and TotalYearlySales <= t3.TotalYearlySales
        ) as RunningSum
    from @Sales t3
    where Yir = Year(getdate())
) sub

这打印:

ItemCode  ItemDesc      TotalYearlySales  Share  ABCIndicator
1234      Yellow Flute  3000              0,36   A
1235      Brown Violin  2000              0,24   A
1236      Silver Flute  1800              0,21   A
1236      Pink Drums    1500              0,18   B