May 13, 2005 at 1:52 pm
I have an inventory table that has a "snapshot" of each days ending inventory. The following columns are the specific columns I am working with:
ProductID (SKU)
SerialNbrId (Specific Piece)
StoreID (Where piece is located)
Datein (Date the piece hit inventory)
Transdate (Date of the Snapshot)
QtyNonSale (Pieces that are received in inventory into non-saleable status)
QtyOnHand (Pieces that are in inventory, including those that are in non-saleable status).
PurchaseOrderID (only used when I am filtering)
ReasonCodeID (Reason for non-saleable).
My task is to pull information that is received into non-saleable status (from returned orders not received purchase orders) that are then moved to sellable product. I can pull the pieces that are received into non-saleable with the following:
select distinct(productid+cast(datein as varchar)+serialnbrid), storeid, qtynonsale, reasoncodeid, serialnbrid
from pieceinventory
where datein between '12-May-05' and '12-may-05'
and transdate = '12-may-05'
and purchaseorderid is null
and qtynonsale > 0
My question is if anyone can think of a way to then pull (by serialnbrid?) those pieces that were once in non-saleable and now are in sellable (the column in qtyonhand, if sellable the reason code then would be null). Any suggestions would be greatly appreciated.
May 16, 2005 at 8:00 am
This was removed by the editor as SPAM
May 16, 2005 at 11:42 am
If I understand you correctly, a set of records which have the same SerialNbrID will have one record with a value in QtyNonSale and and a NULL in QtyOnHand and the ReasonCodeID will have a value. This same SerialNbrID will have another record, (probably with a later TransDate) in which the QryNonSale and ReasonCodeID will be NULL, but QtyOnHand will have a value?.
So are we basically selecting numerous records for the same SerialNbrID and choosing the one's which had a ReasonCodeID, but now have a more current record in the ReasonCodeID is NULL?
I wasn't born stupid - I had to study.
May 16, 2005 at 11:48 am
Exactly, the piece will have a qtynonsellable and a reasoncodeid when received, but then on a later transdaction date the same serial number will now have qty on hand and no reason code id. There is another ugly in that serial nbr ids are not unique, they are however, unique to that productid. That is why I had the concatenation of select(distinct(productid+serialnbrid etc.....)
May 16, 2005 at 1:29 pm
Can you give me a print out of some data? I am too lazy to make my own to veryify what works... Thanks
I wasn't born stupid - I had to study.
May 16, 2005 at 1:44 pm
Sure, here is a very short sample list:
| ProductID | SerialNbrID | StoreID | DateIn | Transdate | QtyNonSale | ReasonCodeID | QtyOnHand | PurchaseOrderID |
| ABC | 1 | 100 | 5/1/2005 | 5/1/2005 | 1 | CLR | 0 | NULL |
| ABC | 1 | 200 | 5/1/2005 | 5/5/2005 | 0 | NULL | 1 | NULL |
| XYZ | 1 | 205 | 5/2/2005 | 5/2/2005 | 1 | FLR | 0 | 12345 |
| XYZ | 1 | 299 | 5/2/2005 | 5/10/2005 | 0 | NULL | 1 | 12345 |
I want to capture product ABC - it was received in without a PO number and then was moved to qtyon hand (qty non sale is 0 and reasoncode is NULL and purchaseorderid is NULL).
Product XYZ was received into a non sale status, but was received on a purchase order and then moved. I want to ignore this because it came in on a PO not a return (purchaseorderid is not NULL).
Thanks so much for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply