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://www.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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