Solved

SQL Server Stored Procedure: Avoiding Using a Cursor?

Posted on 2011-09-19
9
306 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now