Solved

SQL Server Stored Procedure: Avoiding Using a Cursor?

Posted on 2011-09-19
9
312 Views
Last Modified: 2012-05-12
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.outQty)
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

Open in new window


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.
0
Comment
Question by:Candidochris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 36561510
does your invlog has a startup transaction that present the opening balance?
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?
0
 

Author Comment

by:Candidochris
ID: 36561565
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.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36561621
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


;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

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 21

Expert Comment

by:JestersGrind
ID: 36561632
Oops!  Can't have an aggregate in the where clause.  Let me write it another way.

Greg

0
 
LVL 12

Expert Comment

by:FarWest
ID: 36562028
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

0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36562097
This turned out to be a little trickier than I thought it was going to be.  Please try the attached query.

Greg


;WITH Tally
AS
(
SELECT  TOP (90) ROW_NUMBER() OVER (ORDER BY column_id) AS N
FROM sys.columns
),
Data
AS
(
SELECT T1.Code, CONVERT(DATE, CONVERT(DATETIME, DATEDIFF(DAY, T.N, GetDate()))) AS [Date], 
       T1.Stock - ISNULL(SUM(T0.inQty-T0.outQty), 0) AS Quantity
FROM 
	Tally T CROSS JOIN
	INV T1 LEFT OUTER JOIN 
	INVLOG T0 ON T0.Code=T1.Code 
WHERE T0.date >= CONVERT(DATETIME, DATEDIFF(DAY, T.N, GetDate()))
GROUP BY T1.Code, T1.Stock, T.N
)
SELECT  *
FROM  Data
WHERE Quantity <= 0
ORDER BY Code, Date

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36566524
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
0
 

Author Closing Comment

by:Candidochris
ID: 36567424
That is exactly what I need, works great, and relatively quickly.

Thanks Greg.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36567928
Excellent!  Happy to help.

Greg

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question