Solved

SQL Server Stored Procedure: Avoiding Using a Cursor?

Posted on 2011-09-19
9
307 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 36
Script to randomly create characters in MS SQL 6 30
SQL Query 3 48
Rename SQL Instance/SQL Developer Edition 2012 2 9
In this article I will describe the Backup & Restore 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.
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 an introductory video for CloudBerry Managed Backup. You will learn how to sign up with the service and get started in a few minutes.
Both in life and business – not all partnerships are created equal. Spend 30 short minutes with us to learn:   • Key questions to ask when considering a partnership to accelerate your business into the cloud • Pitfalls and mistakes other partners…

911 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

18 Experts available now in Live!

Get 1:1 Help Now