Solved

SQL Server Stored Procedure: Avoiding Using a Cursor?

Posted on 2011-09-19
9
308 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Suggested Solutions

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 I will describe the Detach & Attach 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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

829 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