- #KEY CODE FOR WORKS 8 AND MICROSOFT MONEY 2005 HOW TO#
- #KEY CODE FOR WORKS 8 AND MICROSOFT MONEY 2005 MANUAL#
Select a.stock_number, b.storage_code,a.description, Row_number() over(partition by stock_number order by stock_number nulls last) seqįrom (select a.stock_number, b.storage_code,a.description,Ĭompute_qty_stored(b.warehouse_id,a.stock_number,b.storage_Code) qty_available Select stock_number, storage_code,description,qty_available, Max(decode(seq,4,qty_Available,null)) qty#4 Max(decode(seq,4,storage_code,null)) loc#4, Max(decode(seq,3,qty_Available,null)) qty#1, Max(decode(seq,3,storage_code,null)) loc#3, Max(decode(seq,2,qty_Available,null)) qty#2, Max(decode(seq,2,storage_code,null)) Loc#2, Max(decode(seq,1,qty_Available,null)) qty#1,
Max(decode(seq,1,storage_code,null)) Loc#1, What can i do to get this "resultset" to union with all other stock items that are not in the result set just to be displayed on the report. ALl stock items are listed in stock_item table. I want a master report of all stock items that shows blank entries if a stock item does not have an entry. Problem is that if a stock item is not stored anywhere in "WASHDC" it will be excluded from the result set. I have this query that gives me all storage codes for each stock item for "WASHDC" warehouse. Otherwise - it would seem to me that the answer is 180, not 150.
#KEY CODE FOR WORKS 8 AND MICROSOFT MONEY 2005 HOW TO#
Only if you could tell me exactly (procedurally) how to filter this data (eg: only use data whose effective_date is within 5 days of the max effective_date). Basically you are saying "hey, if there is some long period of time between sets of measurements, ignore the old stuff". I don't even know what that hidden bit of knowledge is myself.
Why would it be 150 and not 180? How would a computer know that - you are using some hidden piece of knowledge in this case. The next uses a "max" trick i use alot to avoid correlated select substr( data, 14, 5 ) item_no, sum( to_number( substr( data, 19 ) ) ) qtyģ select max( to_char( effective_date, 'YYYYMMDD' ) || rpad( bin, 5 ) || rpad( item_no, 5 ) || to_char(qty) ) dataĪnd the last uses a correlated select item_no, sum(qty)ģ where effective_date = ( select max(effective_date)ģ) No, not at all. It uses the analytic functions available with 816 EE and up. The first is the best in my opinion (easy to read, performs select item_no, sum(qty)Ĥ first_value(qty) over(partition by item_no, bin I'll show you at least three different ways to get this. I don't see a question there, only how you answered your own question. The answer here should be 150 and not 180.ġ) that sounds like you are giving me a statement of fact. Would it still work where I basically stored the items in different bins. Also, let me say I have the following table. How do you formulate that statement and exclude all the previous inventories for that item that are out of date.ģ. Bascially I need to run a SQL statement that gives me 200(sum last 4 recrods) for total of an item ∺C006. Here I need to look at the highest effective date for an item at a bin.
which I basically did by taking qty of the item in inventory with the max(effective_Date)+total_received and stored in that bin since that max(effective_date) total shipped from that bin since that max(effective_date).Ģ. Total Qty of an item in inventory in a given bin.
Effective date is the sysdate when record is inserted.ġ.
#KEY CODE FOR WORKS 8 AND MICROSOFT MONEY 2005 MANUAL#
Table look like this where manual inventories are done every 3 months. I have a table that initially stores information about items in a warehouse stored in different bins.