Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Stored Procedure: Avoiding Using a Cursor?

Posted on 2011-09-19
9
Medium Priority
?
317 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

719 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