Candidochris
asked on
SQL Server Stored Procedure: Avoiding Using a Cursor?
I am building a query to determine the stock of an item x days ago, and then the number of days in/out of stock during that time frame.
So, for example, for today, we would go back to 2011-06-21.
I have a table, INVLOG which is a log of inventory transactions.with the following structure:
Code varchar(100), date date, inQty int, outQty int
This table basically tracks all inventory transactions, but it does not give a summary #.
So, if I look up the inventory for today on an item, say Code ABC1, and it comes out to 99 in stock, I can find the inventory X amount of days ago via the following query:
SELECT T1.Stock-SUM(T0.inQty-T0.o utQty)
FROM INVLOG T0
INNER JOIN INV T1 ON T0.Code=T1.Code
WHERE T0.date >= DATEADD(DAY,-90,GETDATE())
(INV is the active inventory table).
Say this number is 7.
Here is my current code to find the days where the item is out of stock:
Any suggestions for a better way to do this? I need to run this for a few thousand items and get the result for each item.
I could not think of a way to do this without a loop.
Thanks in advance.
So, for example, for today, we would go back to 2011-06-21.
I have a table, INVLOG which is a log of inventory transactions.with the following structure:
Code varchar(100), date date, inQty int, outQty int
This table basically tracks all inventory transactions, but it does not give a summary #.
So, if I look up the inventory for today on an item, say Code ABC1, and it comes out to 99 in stock, I can find the inventory X amount of days ago via the following query:
SELECT T1.Stock-SUM(T0.inQty-T0.o
FROM INVLOG T0
INNER JOIN INV T1 ON T0.Code=T1.Code
WHERE T0.date >= DATEADD(DAY,-90,GETDATE())
(INV is the active inventory table).
Say this number is 7.
Here is my current code to find the days where the item is out of stock:
Declare MyCursor cursor local
for
SELECT T0.date,T0.inQty-T0.outQty
FROM INVLOG T0
INNER JOIN INV T1 ON T0.Code=T1.Code
WHERE T0.date >= DATEADD(DAY,-90,GETDATE())
AND (T0.inQty-T0.outQty) IS NOT NULL
AND T0.Code='ABC1'
DECLARE @outdate date;
SET @outdate=NULL;
DECLARE @QueryResults TABLE(
date date,
qty int,
sum int
)
open MyCursor
DECLARE @outdays int;
SET @outdays=0;
declare @fetch_status int
declare @date date;
declare @qty int;
declare @sum int;
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from MyCursor
into
@date,
@qty
SET @start=@start+@qty;
if @start=0 AND @outdate IS NULL
BEGIN
SET @outdate=@date;
END
IF @outdate IS NOT NULL AND @start > 0
BEGIN
SET @outdays=@outdays+DATEDIFF(dd,@outdate,@date);
SET @outdate=NULL;
END
select @fetch_status = @@fetch_status
if @fetch_status <> 0 begin continue end
INSERT INTO @QueryResults VALUES (@date,@qty,@start);
end
close MyCursor
deallocate MyCursor
SELECT @outdays
END
Any suggestions for a better way to do this? I need to run this for a few thousand items and get the result for each item.
I could not think of a way to do this without a loop.
Thanks in advance.
ASKER
If you were to trace invlog back to the beginning (it could be 2008 for some items) the 1st transaction is the initial intake onthe item, so it would have the initial #.
The table is preserved.
So 'yes' to both questions.
The table is preserved.
So 'yes' to both questions.
Obviously I don't have any data to test this, but this is the set based approach that I would take. The query below should be for all codes. Don't have to do them one at a time. It uses a tally table approach to calculate the quantity each day and only show the days where the quantity is less than or equal to 0. I don't know if you have negative quantities or not. That's why I wrote it that way. If you want to go back further than 90 days, change the TOP 90 to however many days that you want.
Greg
Greg
;WITH Tally
AS
(
SELECT TOP (90) ROW_NUMBER() OVER (ORDER BY column_id) AS N
FROM sys.columns
)
SELECT TO.Code, TO.Date
FROM INVLOG T0 INNER JOIN
INV T1 ON T0.Code=T1.Code INNER JOIN
Tally T ON T0.date >= DATEADD(DAY, - T.N,GETDATE())
WHERE T1.Stock-SUM(T0.inQty-T0.outQty) <= 0
ORDER BY TO.Code, TO.Date
Oops! Can't have an aggregate in the where clause. Let me write it another way.
Greg
Greg
in this case it is better if you can modify the application to store additional value in each column for the balance
and you do need a cursor to make update on existing records, because you have to maintain a variable to present the balance after each transaction, after that you just execute very simple and direct select for subsequent queries when you need it in the future,
check this for curser alternative
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx
or you can use a simple program that use dataset to process this functionalilty
good luck
and you do need a cursor to make update on existing records, because you have to maintain a variable to present the balance after each transaction, after that you just execute very simple and direct select for subsequent queries when you need it in the future,
check this for curser alternative
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/CursorsAlternative09012009011823AM/CursorsAlternative.aspx
or you can use a simple program that use dataset to process this functionalilty
good luck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use while lop instead of cursor. its good to do
Or
Select Top1 * from Table
Select Top1 * from Table where Id > PrevId
Select Top1 * from Table where Id < nEXTid
sELECT tOP1 from Table Order by ID desc
Or
Select Top1 * from Table
Select Top1 * from Table where Id > PrevId
Select Top1 * from Table where Id < nEXTid
sELECT tOP1 from Table Order by ID desc
ASKER
That is exactly what I need, works great, and relatively quickly.
Thanks Greg.
Thanks Greg.
Excellent! Happy to help.
Greg
Greg
dose your invlog is preserved i.e. it is not presenting transaction them selves rather that a log of the impact on the item and when the transaction is modified you also add log record for difference, and no delete or update allowed on that table?