How to Compute for ABCIndicator in T-SQL

I have a table of sales data for example:

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

The ShareOfBusiness is computed as the Item's (TotalYearlySales/SUM(TotalYearlySales))*100

The ABCIndicator is A for the list of items whose sum is 80% of the SUM(ShareOfBusiness). B for the next 15% and C for the last 5%.

How can I get the ABCIndicator for each item?

Sample Data:

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 Drum开发者_运维百科s    1500             .18             B

---

You could compute the sum and the running sum in a subquery. The running sum is the total amount of sales for that row and rows with a higher sum.

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

This prints:

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

---

I think your problem will need application of NTILE() function. There is some reading to do here

---

Maybe something like

Select *
    , ABCIndicator = case when ShareOfBusiness > 80 then 'A'
                    when ShareOfBusiness > 65 then 'B'
                    else 'C' --or whatever logic you need
                    End
From
(
    Select *
    ,ShareOfBusiness = MonthlySales/TotalYearlySales*100
    From
    (
        SELECT ItemCode
        , ItemDesc
        , Sum(Sales) TotalYearlySales
        ,Sum(case when Manth = Month(getdate()) then Sales else 0 end) MonthlySales
        --, ShareOfBusiness, ABCIndicator
        FROM Sales
        WHERE Yir = Year(getdate())
        Group By ItemCode, ItemDesc
    ) SalesSummary
) ShareOfBusiness
ORDER BY TotalYearlySales DESC