March 20, 2012 at 12:07 am
I need to calculate the Average stock of my warehouse.
example table :
------------------------------------------
Date-- Item Code-- In-- Out
01/01/2012, A, 200, 0;
15/01/2012, A, 0, 100;
18/01/2012, A, 50, 0;
31/01/2012, A, 0, 20;
by this table i have to calculate the balance like the following:
Date-- Item Code-- Balance
01/01/2012, A, 200;
15/01/2012, A, 100;
18/01/2012, A, 150;
31/01/2012, A, 130;
and after i have to calculate the average stock:
I had for 15 days 200 Kg, for 3 days 100 Kg, for 13 days 150 Kg so in January my average weighted stock was: (((200*15)+(100*3)+(150*13))/15+3+13)= 169,35 Kg and not (200+100+150+130)/4 = 145 Kg.
-------------------------------------------
I would like to do this calculation by SQL, could somebody please give me an help me?
Massimiliano
March 20, 2012 at 12:56 am
As a newbie to the forum you should follow the advice in many of the other postings regarding posting of DDL and sample data. You'll find others are not nearly as forgiving as me.
DECLARE @bal TABLE (
AsOfDateDATETIME
,Item_codeVARCHAR(10)
,BalanceINT)
INSERT INTO @bal
SELECT '2012-01-01' as AsOfDate, 'A' as Item_code, '200' as Balance
UNION ALL SELECT '2012-01-15', 'A', 100
UNION ALL SELECT '2012-01-18', 'A', 150
UNION ALL SELECT '2012-01-31', 'A', 130
SELECT CAST(SUM(Balance * Days) AS DECIMAL(10,2))/CAST(SUM(Days) AS DECIMAL(10,2)) As AvgMonStock
FROM (
SELECT AsOfDate, Item_code, Balance
,DATEDIFF(day
,CASE WHEN AsOfDate = DATEADD(mm, DATEDIFF(mm,0,AsOfDate), 0) THEN DATEADD(day, -1, AsOfDate) ELSE AsOfDaTE END
,(SELECT MIN(AsOfDate) FROM @bal b2 WHERE b2.AsOfDate > b1.AsOfDate)) As Days
FROM @bal b1) x
The next post will be from Jeff Moden warning on the performance dangers of triangular joins. 🙂
BTW. This will only work calculating one month at a time. If other months are in it, you'll need to do something about the WHERE clause in this subquery to eliminate future months.
SELECT MIN(AsOfDate) FROM @bal b2 WHERE b2.AsOfDate > b1.AsOfDate
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
March 20, 2012 at 1:02 am
How about this?
Sample data and table structure
SET DATEFORMAT DMY ;
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
IF OBJECT_ID('TempDB..#Calc') IS NOT NULL
DROP TABLE #Calc;
CREATE TABLE #Temp
(
iD INT IDENTITY(1,1)
, StockDate DATETIME
, ItemCode CHAR(1)
, StockIn INT
, StockIOut INT
);
INSERT INTO #Temp (StockDate , ItemCode , StockIn , StockIOut)
SELECT '01-01-2012', 'A', 200, 0
UNION ALL SELECT '15-01-2012', 'A', 0, 100
UNION ALL SELECT '18-01-2012', 'A', 50, 0
UNION ALL SELECT '31-01-2012', 'A', 0, 20 ;
Code :
IF OBJECT_ID('TempDB..#Calc') IS NOT NULL
DROP TABLE #Calc;
; WITH RecCTE AS
(
SELECT T1.iD , T1.StockDate , T1.ItemCode , T1.StockIn , T1.StockIOut
,Datedifference = 0
,Balance = T1.StockIn
,BalanceForAverage = 0
FROM #Temp T1
WHERE T1.iD = 1
UNION ALL
SELECT Base.iD , Base.StockDate , Base.ItemCode , Base.StockIn , Base.StockIOut
,Datedifference = DATEDIFF (DD ,Rec.StockDate, Base.StockDate)
,Balance = Rec.Balance - Base.StockIOut + Base.StockIn
,BalanceForAverage = Rec.Balance
FROM RecCTE Rec
INNER JOIN #Temp Base
ON Rec.iD + 1 = Base.iD
)
SELECT *
, Mth = MONTH(StockDate)
INTO #Calc
FROM RecCTE;
SELECT StockDate , ItemCode , Balance
FROM #Calc
SELECT C.ItemCode , C.Mth
,Average = SUM(C.BalanceForAverage * C.Datedifference * 1.00) / SUM (C.Datedifference)
FROM #Calc C
GROUP BY C.ItemCode , C.Mth
March 20, 2012 at 1:05 am
Mr. ColdCoffee - I love the * 1.00 to eliminate the awkward CAST I used in my version!
Gotta remember little tricks like this.
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
March 20, 2012 at 1:05 am
You may also want to check out Jeff Moden article on arguably the fastest method in calculating running-totals as the one your requirements is putting up.
Please read it here : http://www.sqlservercentral.com/articles/T-SQL/68467/
March 20, 2012 at 1:17 am
dwain.c (3/20/2012)
Mr. ColdCoffee - I love the * 1.00 to eliminate the awkward CAST I used in my version!Gotta remember little tricks like this.
Yep, that's a neat one. Picked up from here sometime back 🙂 You gotta love this site, dont you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply