July 15, 2010 at 1:06 pm
Guys
This is a SQL 2000 problem.
I have tried searching goggle/this forum to no avail (maybe using wrong terminology)
Problem:
I need to calculate the average number of days we have held individual stock items.
For example
Current Stock = 50
Last stock receipts (in desc date)
30 received 10 days ago
20 received 45 days ago
Therefore average stock days = 24 = ( (30*10) + (20*45) ) / 50
Can anyone please direct me to a suitable post/article that deals with this issue?
Alternatively I can post neccessary table defs / data / expected results if preferred.
Thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 15, 2010 at 3:56 pm
It depends on your table design.
Here's a rather simple example using the sample data you provided:
DECLARE @tbl TABLE
(
article_id INT,
received INT,
age INT
)
INSERT INTO @tbl
SELECT 1,30 , 10 UNION ALL
SELECT 1,20 , 45
SELECT article_id, SUM(received*age)/SUM(received) AS avg_stock_days
FROM @tbl
GROUP BY article_id
If you want to look deeper into the subjecr or don't have the data available in the format you posted you might want to have a look at The ‘FIFO Stock Inventory’ SQL Problem challenge. This challenges covers a similar issue. The suggested solutions should give you something to start with.
July 16, 2010 at 11:59 am
Hi Lutz
Thanks for the link....several useful ideas for me to consider.
As always, the devil is in the detail, and I am sure you will appreciate that the example I gave was way over simplified.
Regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 16, 2010 at 2:55 pm
If you start using one of the ideas posted in the challenge I referred to and you get stuck, let us know what you're struggling with and I'm sure there are a few people around being able to help you.
But we would need a more detailed explanation of the table structure including sample data and expected result otgether with what you've tried so far. If you need a guideline how to post sample data please see the first link in my signature.
July 18, 2010 at 8:14 am
Hi Lutz
well having done some homework....I have a solution, but would appreciate any feedback on how to improve it.
For new readers this is SQL 2000 and the necessary code is at the end of this post.
Some further background on the production dB as follows:
The tables are replicated in SQL from a third party app.
Stock_Details table...this is approx 100K rows and the Current_Stock column is updated for each and every transaction in Stock_Transaction.
Stock_Transactions table...approx 20M rows...this hold details for each stock movement (IN/OUT/RETURN/ADJ etc)
As we already know the current stock there is no requirement to calculate running totals and therefore the only details required are the "IN" movements and these are what I include in the code below.
To understand what I am trying to acheive here is a simple example
Current Stock = 50
Last stock receipts (in desc date)
30 received 10 days ago
80 received 45 days ago
Therefore average stock days = 24 = ( (30*10) + (20*45) ) / 50
Please note here that the second receipt of 80 only uses the balance of 20 required for the calculation.
I have used Jeff Modem's "quirky update"...from the link you kindly provided and think I have followed the rules !
so here is the code....please be kind :-)...at the moment this is proof of concept and I have not had opportunity to test on production yet....but I am looking to have a solution that performs better than the app provider code (2 hours)
many thanks for taking an interest
also a sincere thanks to Jeff Modem for his article
Regard Graham
USE [TempDB]
GO
--===== Create Stock Details table...this contains current stock levels and therefore no requirement to create a running total temp table
IF OBJECT_ID('TempDB.dbo.Stock_Details') IS NOT NULL
DROP TABLE TempDB.dbo.Stock_Details
CREATE TABLE [Stock_Details](
[ProductID] [int] NOT NULL,
[Current_Stock] [int] NOT NULL,
)
--====insert some data
INSERT INTO [dbo].[Stock_Details]([ProductID], [Current_Stock])
SELECT 1000, 100 UNION ALL
SELECT 1100, 65 UNION ALL
SELECT 1200, 30 UNION ALL
SELECT 1300, 10 UNION ALL
SELECT 1400, 0
--==== Create Stock Transaction table this would be based on a subset of all transactions from production table
--==== production table contains all transactions...IN/OUT/RETURNS/ADJS etc
IF OBJECT_ID('TempDB.dbo.Stock_Transactions') IS NOT NULL
DROP TABLE TempDB.dbo.Stock_Transactions
CREATE TABLE [Stock_Transactions](
[ProductID] [int] NOT NULL,
[Purchase_date] [datetime] NOT NULL,
[Quantity] [int] NOT NULL,
[TransID] [int] NOT NULL,
)
--====insert some data
INSERT INTO [dbo].[Stock_Transactions]([ProductID], [Purchase_date], [Quantity], [TransID])
SELECT 1300, '20091015 00:00:00.000', 20, 2145 UNION ALL
SELECT 1200, '20091031 00:00:00.000', 50, 5501 UNION ALL
SELECT 1300, '20091130 00:00:00.000', 18, 7895 UNION ALL
SELECT 1200, '20100205 00:00:00.000', 10, 10563 UNION ALL
SELECT 1100, '20100315 00:00:00.000', 20, 12586 UNION ALL
SELECT 1100, '20100315 00:00:00.000', 40, 12601 UNION ALL
SELECT 1200, '20100601 00:00:00.000', 20, 16532 UNION ALL
SELECT 1000, '20100603 00:00:00.000', 20, 17562 UNION ALL
SELECT 1400, '20100605 00:00:00.000', 50, 18015 UNION ALL
SELECT 1000, '20100615 00:00:00.000', 20, 18996 UNION ALL
SELECT 1000, '20100701 00:00:00.000', 60, 20156 UNION ALL
SELECT 1100, '20100712 00:00:00.000', 10, 22651 UNION ALL
SELECT 1300, '20100715 00:00:00.000', 2, 24015
--==== create a working table..in production this would be a temp table but for debug is created in TempDB
IF OBJECT_ID('TempDB.dbo.temp_Avdays') IS NOT NULL
DROP TABLE TempDB.dbo.temp_Avdays
SELECT
dbo.Stock_Transactions.ProductID, dbo.Stock_Transactions.Quantity,
DATEDIFF(d, dbo.Stock_Transactions.Purchase_date,
GETDATE()) AS PurchaseDaysAge, dbo.Stock_Transactions.Purchase_date,
dbo.Stock_Transactions.TransID, dbo.Stock_Details.Current_Stock,
dbo.Stock_Details.Current_Stock AS RunBalance,
dbo.Stock_Transactions.Quantity * DATEDIFF(d, dbo.Stock_Transactions.Purchase_date, GETDATE()) AS StockDays
INTO dbo.temp_Avdays
FROM dbo.Stock_Transactions INNER JOIN
dbo.Stock_Details ON dbo.Stock_Transactions.ProductID = dbo.Stock_Details.ProductID
--===== Create clustered Index for "quirky update"
ALTER TABLE [temp_Avdays] ADD CONSTRAINT [IX_temp_Avdays] UNIQUE CLUSTERED
(
[ProductID] ASC,
[Purchase_date] DESC,
[TransID] DESC
)
--===== following two sections use Mr J.Modem's "quirky update"
--===== full details can be found here http://www.sqlservercentral.com/articles/T-SQL/68467/
--===== not sure if these steps can be incorporated into one update statement..??
--===== Declare the working variables to update Runbalance...this is the qty left to "find"
DECLARE @ProductID INT
DECLARE @Runbalance INT
UPDATE [dbo].[temp_AVDays]
SET @runbalance = runbalance = CASE WHEN ProductId = @ProductID
THEN @runbalance - Quantity
ELSE Current_Stock - QUANTITY
END,
@ProductId = ProductID
FROM [dbo].[temp_AvDAys] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
--===== Declare the working variables to update StockDays...recalcs stockdays to accomodate where next receipt is greater than the runbalance
DECLARE @StockDays INT
DECLARE @RunningBalance INT
UPDATE [dbo].[temp_AVDays]
SET @StockDays = Stockdays = CASE WHEN RunBalance >= 0
THEN Quantity * PurchaseDaysAge
ELSE @RunningBalance * PurchaseDaysAge
END,
@RunningBalance = RunBalance
FROM [dbo].[temp_AvDAys] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
--===== Get Results.....ignore any rows where stockdays is negative.
SELECT S.ProductID, ROUND(CAST(D.SumStockDays AS numeric) / CAST(S.Current_Stock AS numeric), 1) AS AvStockdays
FROM dbo.Stock_Details S
LEFT OUTER JOIN (SELECT ProductID, SUM(StockDays) AS SumStockDays
FROM dbo.temp_Avdays
WHERE (StockDays >= 0)
GROUP BY ProductID) D ON S.ProductID = D.ProductID
GO
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2010 at 9:32 am
Hi gah,
GREAT JOB!!!!
As far as I can see, this solution will work.
I would make two changes:
#1: you need to cover the scenario where your stock is less than the amount received last. E.g. set the CurrentStoc for ProductID to 1 and run the code. You'll end up with a NULL value for StockDays...
#2: I think it's safe to merge the two updates. However, you should test it against a larger data set and verify the results to be identical.
So, the quirky update code would look like
DECLARE @ProductID INT
DECLARE @Runbalance INT
DECLARE @StockDays INT
DECLARE @RunningBalance INT
UPDATE [dbo].[temp_AVDays]
SET
@runbalance = runbalance = CASE WHEN ProductId = @ProductID
THEN @runbalance - Quantity
ELSE Current_Stock - QUANTITY
END,
@StockDays = Stockdays = CASE WHEN @runbalance >= 0
THEN Quantity * PurchaseDaysAge
ELSE ISNULL(@RunningBalance,Current_Stock) * PurchaseDaysAge
END, -- to cover the scenario when CurrentStock < last Quantity
@RunningBalance = RunBalance,
@ProductId = ProductID
FROM [dbo].[temp_AvDAys] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
July 18, 2010 at 10:30 am
LutzM (7/18/2010)
Hi gah,GREAT JOB!!!!
As far as I can see, this solution will work.
I would make two changes:
#1: you need to cover the scenario where your stock is less than the amount received last. E.g. set the CurrentStoc for ProductID to 1 and run the code. You'll end up with a NULL value for StockDays...
#2: I think it's safe to merge the two updates. However, you should test it against a larger data set and verify the results to be identical.
Thanks Lutz
#1...very good point, thank you...will need to give this some more thought, tho at the moment I cant figure this out...been a long day 😀
#2...initially getting errors...but need to work these thro.
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2010 at 12:54 pm
gah (7/18/2010)
also a sincere thanks to Jeff Modem for his article
It's actually spelled "Moden" but I'll take the compliment. 🙂 Thanks, Graham.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2010 at 1:06 pm
Jeff Moden (7/18/2010)
gah (7/18/2010)
also a sincere thanks to Jeff Modem for his articleIt's actually spelled "Moden" but I'll take the compliment. 🙂 Thanks, Graham.
:blush: apologies Jeff...and the compliment still stands.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2010 at 1:09 pm
gah (7/18/2010)
LutzM (7/18/2010)
Hi gah,GREAT JOB!!!!
As far as I can see, this solution will work.
I would make two changes:
#1: you need to cover the scenario where your stock is less than the amount received last. E.g. set the CurrentStoc for ProductID to 1 and run the code. You'll end up with a NULL value for StockDays...
#2: I think it's safe to merge the two updates. However, you should test it against a larger data set and verify the results to be identical.
Thanks Lutz
#1...very good point, thank you...will need to give this some more thought, tho at the moment I cant figure this out...been a long day 😀
Guys...anyone care to help me out on Lutz's #1...seem to be strugglng at the mo
any ideas most appreciated...thanks
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2010 at 3:12 pm
gah (7/18/2010)
gah (7/18/2010)
...Guys...anyone care to help me out on Lutz's #1...seem to be strugglng at the mo
any ideas most appreciated...thanks
Graham
????
I'm kinda confused...
The code I provided include the following line:
ELSE ISNULL(@RunningBalance,Current_Stock) * PurchaseDaysAge
Based on your sample code you didn't assign a inital value to @RunningBalance.
So, in case of runbalance (or @runbalance) < 0 you'll run this ELSE condition with a NULL value assigned to @RunningBalance. In this case the code would calculate Current_Stock * PurchaseDaysAge.
Side note: I edited the sample code not to have that many tab character in there...
Btw: what's the rror code you get when running the sample I posted? It did run just fine when I tested it....
July 19, 2010 at 9:31 am
????
I'm kinda confused...
The code I provided include the following line:
ELSE ISNULL(@RunningBalance,Current_Stock) * PurchaseDaysAgeBased on your sample code you didn't assign a inital value to @RunningBalance.
So, in case of runbalance (or @runbalance) < 0 you'll run this ELSE condition with a NULL value assigned to @RunningBalance. In this case the code would calculate Current_Stock * PurchaseDaysAge.
Side note: I edited the sample code not to have that many tab character in there...
.
....that explains it.....didnt see your full code in my browser....will take another look later.
Thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2010 at 10:03 am
I think the solution I posted will not cover all scenarios (e.g. if the 2nd or 3rd ProductID will have a stock less than the amount received last).
I'll have a look at it later on today.
July 19, 2010 at 11:11 am
LutzM (7/19/2010)
I think the solution I posted will not cover all scenarios (e.g. if the 2nd or 3rd ProductID will have a stock less than the amount received last).I'll have a look at it later on today.
Hi Lutz
Unfortuantely you are correct in your assumption...the code throws NULLS.
will take a deeper look later today
Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2010 at 1:41 pm
I think the following solution will cover all scenarios:
DECLARE @ProductID INT
DECLARE @Runbalance INT
DECLARE @StockDays INT
DECLARE @RunningBalance INT
UPDATE [dbo].[temp_AVDays]
SET
@runbalance = runbalance = CASE WHEN ProductId = @ProductID
THEN @runbalance - Quantity
ELSE Current_Stock - QUANTITY
END,
@StockDays = Stockdays = CASE WHEN @runbalance >= 0
THEN Quantity * PurchaseDaysAge
WHEN @runbalance < 0 AND ProductId <> ISNULL(@ProductID,-1)
THEN Current_Stock * PurchaseDaysAge
ELSE @RunningBalance * PurchaseDaysAge
END,
@ProductId = ProductID,
@RunningBalance = @runbalance
FROM [dbo].[temp_AvDAys] WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply