February 6, 2009 at 3:27 pm
I have a table that I want to sum the records in order until my qty is met. What is the most efficient way to do this?
I have written a while loop similar to what is below to do this. It works but is slow when I call it as a function.
Any suggestions on better ways would be appreciated. Thanks.
DECLARE
@item NVARCHAR(30)
, @NeedQty DECIMAL(18,8)
, @Loop TINYINT
, @key INT
, @Qty DECIMAL(18,8)
, @Cost DECIMAL(18,8)
, @RunTot DECIMAL(18,8)
SET @Item = '305275'
SET @Loop = 1
SET @Runtot = 0
SET @key = 0
DECLARE @MyData TABLE (
tt_key INT IDENTITY(1,1)
, item NVARCHAR(30)
, qty DECIMAL(18,8)
, cost DECIMAL(18,8)
)
INSERT INTO @MyData
SELECT
item
, qty
, cost
FROM matltran
WHERE item = '305275'--@Item
ORDER BY trans_num DESC
WHILE (@Loop = 1)
BEGIN
SET @Qty = NULL
SELECT TOP 1
@key = tt_key
, @qty = qty
, @Cost = cost
FROM @MyData
WHERE tt_key > @key
ORDER BY tt_key ASC
IF @Qty IS NULL
BEGIN
SET @Loop = 0
END
ELSE
BEGIN
SET @RunTot = @RunTot + @Qty
IF @RunTot >= @NeedQty
BEGIN
SET @Loop = 0
END
END
END
SELECT @RunTot
February 6, 2009 at 4:50 pm
Jeff Moden wrote an article on this site that addresses the issue of Running Totals. However, he apparently has pulled the article to correct some issues he found with it. I am still going to refer you to that article, because there is a link to the code that will help you out.
The article is: http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
After reviewing the code, post your questions about it and somebody here will definitely help you out.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2009 at 10:05 pm
This might help:
-- populating test data
DECLARE @matltran TABLE (trans_num INTEGER IDENTITY
,item NVARCHAR(30)
,qty DECIMAL(18,8)
,cost DECIMAL(18,8))
DECLARE @x TINYINT
SET @x = 1
WHILE @x < 200
BEGIN
INSERT INTO @matltran (item, qty, cost)
SELECT 'Test1', 5.5, 0.7
UNION ALL
SELECT 'Test2', 3.75, 0.6
SET @x = @x + 1
END
--actual select
DECLARE @NeedQty DECIMAL(18,8)
SET @NeedQty = 500
SELECT MIN(RunningTotal), item
FROM (SELECT trans_num
,item
,qty
,(SELECT SUM(qty)
FROM @matltran b
WHERE b.item = a.item
AND b.trans_num >= a.trans_num) AS RunningTotal
,cost
FROM @matltran a) c
WHERE c.RunningTotal >= @NeedQty
GROUP BY item
February 7, 2009 at 12:52 pm
To me, CTE (Common Table Expressions) are best for recursion and running totals.
Fraggle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply