Help with Cursor and Fetch

[Parabolic33]

Hi,

I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.

The first temp table that I am getting data from contains the following fields.

ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.

The information contained in the table references all of the times our warehouse received certain items. What I need is to have the new temp table populated with the number of receipts where the Sum of the QtyReceived equals the QtyOnHand. For example, if I currently have 100 pieces on hand and I have 6 receipts in the table, each with a QtyReceived of 25 pieces, I only want the last four receipt records moved into the second temp table.

The current script I have written is below. If you can offer any help I would greatly appreciate it.

Thanks,
Declare @ItemID VarChar(30),
@QtyOnHand Decimal (16,8),
@WhseID VarChar (6),
@SumRcvd Int,
@TranID VarChar(30),
@TranDate DateTime,
@QtyRcvd Decimal (16,8),
@UnitCost Decimal (16,8),
@ItemKey Int,
@WhseKey Int,
@ShortDesc VarChar (40),
@StdCost Decimal (16,8)

DECLARE Temp_cursor CURSOR FOR
SELECT TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost
FROM #Temp1 tem

OPEN Temp_cursor

FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost


WHILE @@FETCH_STATUS = 0

BEGIN -- 0

Insert Into #Temp3
(TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost)

Values
(@TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost)

FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost


End

CLOSE Temp_cursor
DEALLOCATE Temp_cursor

 

[SQLSister]

Do not under any circumstances use a cursor for this. You should not be using cursors at all in SQL server unless there is no other choice. If all you are doing is inserting the values from #temp1 into #temp3, why not just use #temp1 all along?

If there is some where clause here that you simplified out fo the cursor select then use a set-based insert instead of the cursor. Sample of the syntax for that.

insert into #temp3 (filed1, field 2, field3)
select field1, field2, filed3 from #temp1 where ...

Cursors are huge perfomance killers and should not be used for insert, update or delete statements.

"NOTHING is more important in a database than integrity." ESquared

 

[Parabolic33]

I can't use a simple where statment because I need to get multiple records for one item. This is the kind of information I have which I am trying to get.

ReceiptNo ReceiptDate ItemNo WhseID QtyRecvd QtyOnHand
0001 01/01/08 456 050 10 20
0002 01/05/08 456 050 05 20
0003 01/08/08 456 050 05 20
0004 01/10/08 456 050 10 20

I need the cursor to grab the last three Receipt Numbers, 0004, 0003, 0002 since the sum of their QtyRecvd = the QtyOnHand. The QtyOnHand isn't by Receipt number, it is the total quantity on hand for that item in that warehouse.

Does that make more sense?

 

[bborissov]

Why not First and Fourth?
Their SUM(QtyRecvd) is also equal to QtyOnHand.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Parabolic33]

It will eventually get fed into a crystal report which will show the total Qty on hand for an item in a particular warehouse at the Std Cost. Everything we inventory is based on a Std Cost, but there is a variance at times during the purchase price and the inventoried price.

What my accounting department needs to see is a breakdown of the Qty on Hand at the Std Cost as well as the quantities which where received from newest to oldest and their actual costs.

Does that make sense?

 

[k01]

You can get the result by this query.

SELECT
   A.ReceiptNo
  ,A.ReceiptDate
  ,A.ItemNo
  ,A.WhseID
  ,A.QtyRecvd
  ,A.QtyOnHand
FROM
  #Temp1 A
    INNER JOIN
      (
      SELECT 
          ReceiptNo
        FROM 
          #Temp1 T1
        WHERE
          QtyOnHand = (SELECT SUM(QtyRecvd) FROM #Temp1 WHERE ReceiptNo >= T1.ReceiptNo)
        ) B    
    ON
      A.ReceiptNo >= B.ReceiptNo

koichi

http://aboutabi.blogspot.com/

 

[Parabolic33]

When I run that query it returns an empty record set.

 

[ca8msm]

You should provide create scripts and sample data for your database tables so people can see what should be returned and perform their own tests.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Parabolic33]

In Reality, all I am trying to find out is where I put in the criteria for IF statements in my fetch command. Going back to the original question here is what I need.

I have a temp table populated with all of these headers and data.

The headers are as follows:
ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.

The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd equals or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.

One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.

The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.

Please see the script currently being used on my initial post. Again, the Cursor and Fetch statement I am using works in getting all of the records put into the new temp table. I just need to find out how to put in the criteria to only pull the data I need into this temp table.

Thank you