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
精彩评论