June 18, 2012 at 4:48 am
How to I assign an ABC category to a product based on its current stock holding?
If I know my total stock holding is £1000, how do I list the first £800 worth of stock in descending order of stock value? Then the next £150 then £50
My data table has the following data and I know my ABC breakdown is £800 for A, £150 for B and everything else is C
PROD1, £600
PROD2, £200
PROD3, £100
PROD4, £50
PROD5, £25
PROD6, £20
PROD7, £5
So my SQL would return
PRODUCT ABC
PROD1 A
PROD2 A
PROD3 B
PROD4 B
PROD5 C
PROD6 C
PROD7 C
June 18, 2012 at 6:01 am
You should read the article (link at the bottom of my signature) about how to present questions like that to help your helpers to help you 😉
create table #MyDataTable ( code char(10), value money)
insert #MyDataTable
select 'PROD1', £1000
union select 'PROD2', £800
union select 'PROD3', £200
union select 'PROD4', £150
union select 'PROD5', £25
union select 'PROD6', £20
union select 'PROD7', £5
select code, case when value>=800 then 'A'
when value>=150 then 'B'
else 'C'
end ABC
from #MyDataTable
order by value desc, code
BTW, sample data you provided wouldn't produce the specified output as it doesn't qualify for given break-downs.
June 18, 2012 at 10:38 pm
Eugene Elutin (6/18/2012)
You should read the article (link at the bottom of my signature) about how to present questions like that to help your helpers to help you 😉
create table #MyDataTable ( code char(10), value money)
insert #MyDataTable
select 'PROD1', £1000
union select 'PROD2', £800
union select 'PROD3', £200
union select 'PROD4', £150
union select 'PROD5', £25
union select 'PROD6', £20
union select 'PROD7', £5
select code, case when value>=800 then 'A'
when value>=150 then 'B'
else 'C'
end ABC
from #MyDataTable
order by value desc, code
BTW, sample data you provided wouldn't produce the specified output as it doesn't qualify for given break-downs.
Actually Eugene, I think what the OP is looking for is a variant on the cumulative totals problem. This solution may not work entirely but it may give you a start.
create table #MyDataTable ( code char(10), value money, cumtot money, threshold money)
insert #MyDataTable (code, value)
select 'PROD1', £600
union select 'PROD2', £200
union select 'PROD3', £100
union select 'PROD4', £50
union select 'PROD5', £25
union select 'PROD6', £20
union select 'PROD7', £5
DECLARE @cumtot MONEY = 0
,@threshold MONEY = 0
UPDATE m
SET @cumtot = cumtot = @cumtot + value
FROM #MyDataTable m
UPDATE m
SET @threshold = threshold =
CASE WHEN @threshold <= 800 AND cumtot <= 800 THEN 800
WHEN @threshold <= 800 AND cumtot <= 950 THEN 150
ELSE 50 END
FROM #MyDataTable m
SELECT code, value, ABC=CASE threshold WHEN 800 THEN 'A' WHEN 150 THEN 'B' ELSE 'C' END
FROM #MyDataTable
DROP TABLE #MyDataTable
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 4:11 am
Absolutely brilliant solution.
Fits my requirement's 100%.
Many thanks.
June 19, 2012 at 4:33 am
Kelvin Phayre (6/19/2012)
Absolutely brilliant solution.Fits my requirement's 100%.
Many thanks.
Well thank you kind Sir Kelvin!
You do need to be careful about a couple of things though:
1. If you are doing this across different sets of IDs, record ordering may come into play. Traversing the records in order of descending amount may be a challenge.
2. If you are doing this to a permanent table, I'd suggest selecting it into a temp table first. The reason for this is not apparent but is buried in this article: http://qa.sqlservercentral.com/articles/T-SQL/68467/. Look for the part about using a clustered index and this stuff:
WITH (TABLOCKX) OPTION (MAXDOP 1)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 4:49 am
I've used option 2 and inserted into a predefined table that I truncate first and then insert via select with order by value descending.
Works a treat.
Very clever stuff!
Always nice to see new SQL usage.
June 19, 2012 at 6:22 am
And in case anybody calls me a one trick pony, here's another way to do it.
But I do need to warn of the hidden RBAR that you can find explained here: http://qa.sqlservercentral.com/articles/T-SQL/74118/, otherwise Mr. Jeff Moden (author) will be whuppin' on my butt.
create table #MyDataTable (ID INT, code char(10), value money, cumtot money, threshold money)
insert #MyDataTable (ID, code, value)
select 1, 'PROD1', £600
union select 1, 'PROD2', £200
union select 1, 'PROD3', £100
union select 1, 'PROD4', £50
union select 1, 'PROD5', £25
union select 1, 'PROD6', £20
union select 1, 'PROD7', £5
union select 2, 'PROD1', £500
union select 2, 'PROD2', £250
union select 2, 'PROD3', £100
union select 2, 'PROD4', £75
union select 2, 'PROD5', £50
union select 2, 'PROD6', £25
;WITH InputData AS (
SELECT ID, code, value
,rn=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value DESC)
FROM #MyDataTable
),
CumTotals AS (
SELECT ID, code, value, CumTot=value, rn, threshold=800
FROM InputData
WHERE rn = 1
UNION ALL
SELECT id.ID, id.code, id.value, CumTot + id.value, id.rn
,CASE WHEN ct.threshold <= 800 AND CumTot < 800 THEN 800
WHEN threshold <= 800 AND CumTot < 950 THEN 150
ELSE 50 END
FROM CumTotals ct
INNER JOIN InputData id
ON ct.ID = id.ID AND ct.rn + 1 = id.rn
)
SELECT ID, code, value, ABC=CASE threshold WHEN 800 THEN 'A' WHEN 150 THEN 'B' ELSE 'C' END
FROM CumTotals
ORDER BY ID, code
DROP TABLE #MyDataTable
Note that I've added an ID for partitioning purposes to make it interesting. This solution, while most likely slower, doesn't come with the caveats (extra columns, ordering by clustered index, etc.) of the quirky update I used the first time.
Depending on your perspective, it might even be easier to understand.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 7:09 am
Not being one to rest on past accomplishments, I've improved both versions:
create table #MyDataTable (ID INT, code char(10), value money, cumtot money, threshold money)
insert #MyDataTable (ID, code, value)
select 1, 'PROD1', £600
union select 1, 'PROD2', £200
union select 1, 'PROD3', £100
union select 1, 'PROD4', £50
union select 1, 'PROD5', £25
union select 1, 'PROD6', £20
union select 1, 'PROD7', £5
-- Alternate #1: Quirky update to get cumulative totals
DECLARE @cumtot MONEY = 0
,@threshold MONEY = 0
UPDATE m
SET @cumtot = cumtot = @cumtot + value
FROM #MyDataTable m
-- This update no longer needed
--UPDATE m
--SET @threshold = threshold =
-- CASE WHEN @threshold <= 800 AND cumtot <= 800 THEN 800
-- WHEN @threshold <= 800 AND cumtot <= 950 THEN 150
-- ELSE 50 END
--FROM #MyDataTable m
SELECT code, value, ABC=
CASE WHEN cumtot <= 800 THEN 'A'
WHEN cumtot <= 950 THEN 'B'
ELSE 'C' END
FROM #MyDataTable
insert #MyDataTable (ID, code, value)
select 2, 'PROD1', £500
union select 2, 'PROD2', £250
union select 2, 'PROD3', £100
union select 2, 'PROD4', £75
union select 2, 'PROD5', £50
union select 2, 'PROD6', £25
-- Solution #2: Improved version
;WITH InputData AS (
SELECT ID, code, value
,rn=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value DESC)
FROM #MyDataTable
),
CumTotals AS (
SELECT ID, code, value, CumTot=value, rn, ABC='A'
FROM InputData
WHERE rn = 1
UNION ALL
SELECT id.ID, id.code, id.value, CumTot + id.value, id.rn
,CASE WHEN CumTot < 800 THEN 'A'
WHEN CumTot < 950 THEN 'B'
ELSE 'C' END
FROM CumTotals ct
INNER JOIN InputData id
ON ct.ID = id.ID AND ct.rn + 1 = id.rn
)
SELECT ID, code, value, ABC
FROM CumTotals
ORDER BY ID, code
DROP TABLE #MyDataTable
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 7:32 am
Last time for tonight I promise. Here's the killer version:
create table #MyDataTable (ID INT, code char(10), value money, cumtot money, threshold money)
insert #MyDataTable (ID, code, value)
select 1, 'PROD1', £600 union select 1, 'PROD2', £200 union select 1, 'PROD3', £100
union select 1, 'PROD4', £50 union select 1, 'PROD5', £25 union select 1, 'PROD6', £20
union select 1, 'PROD7', £5 union select 2, 'PROD1', £500 union select 2, 'PROD2', £250
union select 2, 'PROD3', £100 union select 2, 'PROD4', £75 union select 2, 'PROD5', £50
union select 2, 'PROD6', £25
-- Solution #3: Killer version
DECLARE @ID INT = 0, @CumTot MONEY = 0
;WITH InputData AS (
SELECT ID, code, value, CumTot
,rn=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value DESC)
FROM #MyDataTable)
UPDATE m
SET @cumtot=cumtot=CASE WHEN ID = @ID THEN @cumtot + value ELSE value END, @ID=ID
OUTPUT INSERTED.ID, INSERTED.code, INSERTED.value
,CASE WHEN INSERTED.CumTot <= 800 THEN 'A'
WHEN INSERTED.CumTot <= 950 THEN 'B'
ELSE 'C' END AS ABC
FROM InputData m
DROP TABLE #MyDataTable
One step, no recursion.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply