How To Maintain Transaction Order For Perpetual Inventory
Feb 9, 2008
Hello
I would appreciate If any one could help me in this matter.
problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.
I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).
version used : SQL Server 2000 with service pack updates.
ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.
Hello, I'm looking for a little input on this situation.I'm working on an inventory system and was thinking that I'd like tobuild it similiar to the way a bank keeps track of your funds.If you look at at bank statement, for each line item, you'll normallysee at least date/time, description, amount (plus or minus value), andbalance.In my inventory system, the amount and balance would be a count of theitems used or transferred instead of money.Does this sound reasonable? Or is there a better design? I am planningto connect the inventory system to a few different ordering interfaces,and I think it would work well if I can dump transactions into theinventory instead of incrementing and decrementing counts.The only problem I see with this, is if I want to implement the balancepart of it with each record....I can't think of a way to do that currently.I can certainly use a trigger in the database to retrieve the mostrecent transaction and update the balance field for one record insertedat a time, but if a list of transactions is dumped into the inventorysystem at once, I'd have to compensate for that in the trigger.Ideas?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Say I have a result set with two fields numbers and letters.
1 A3 A1 B2 B
The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet
it will reorder the new result set by the Number field and return
123
However, I'd like maintain the Letter order and return
Hi All, Pandon me for asking such question, I am still a beginner to ASP.NET. I have a project that require me to do single operation that is suppose to update two databases, wonder how do I maintain transaction between these two databases? Please advise, thank you!
Is the order of execution guaranteed to go from top to bottom in a transaction that has multiple statements like below?
BEGIN TRAN T1; UPDATE table1 ...; UPDATE table2 ...; SELECT * from table1; UPDATE table3 ...; COMMIT TRAN T1;
How about here?
BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 ; UPDATE table2 ...; SELECT * from table1; COMMIT TRAN M2; UPDATE table3 ...; COMMIT TRAN T1;
how can i guarantee that statements will be executed from top to bottom in a transaction batch like above? I am not interested in the errors in statements. I just want whole thing to either execute fully from top to bottom or none executes
Hi! I'm kinda stuck in getting a particularly tricky qurey...
SQL Code:
Original - SQL Code
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID WHERE (((Orders.ShippedDate) Between #1/1/1995# And #12/31/1995#)) GROUP BY Categories.CategoryName, Products.ProductName;
SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, SUM(CCur([ORDER Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSalesFROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [ORDER Details] ON Orders.OrderID = [ORDER Details].OrderID) ON Products.ProductID = [ORDER Details].ProductIDWHERE (((Orders.ShippedDate) BETWEEN #1/1/1995# And #12/31/1995#))GROUP BY Categories.CategoryName, Products.ProductName;
The above SQL statement is from the famous Northwind DB and it is called the "Product Sales for 1995" query...
I have Transactions instead of Orders and TransactionDetails instead of [Order Details]
Now, I want something similar... I want to render Dead Stock and Fast Stock... I'm using ComponentOne's Reporting solution and it pretty much like Access.
Anyways, I want to supply a date for TransactionDetail.TransactionDetailDetail as starting date (ending date is always Today()) and within this range, i want to get a list of products that are sorted ascending on Products.ProductUnitSold. This should return a list of products (grouped by category name) showing SubCategoryName, ProductName, ProductUnitPrice, TotalSold...
If total sold is ascending = Dead Stock If total sold is descending = Fast Stock
Hi all, i don't know where to post these, and so i posted in here about sql stuff... I want to do a stock inventory for my restaurant, and i don't know how to start building the database, so, I want to ask if anyone knows if they have a database diagram for stock inventory... any kind of database diagram will helps, so I get and Idea how to start... thanks...
i want to make business portal intellegence with sql2000 as my final project but i don't have datasets. i need iventory datasets like northwind or foodmart or middlewest. somebody help me please!!
I am designing a BI solution for a large manufacturer. They process big ticket items. They track their inventory by each piece. I need to know where each item is located, where it came from, when it arrived, has it been processed, ect. Along with that I have to be able to run reports by day/week/month/year that shows current inventory levels at any point in time.
What I arrived at for table design is each item will get a row which will include a column for date and another for Add_Remove. When the item is added to inventory it gets a date and a +1 for Add_Remove. When it is removed it gets a new row with a date and a -1 in the Add_Remove column. I should be able to add the Add_Remove column for the selected rows (by location, type, etc) from the beginning of time to any point in time and get a current total. If I'm reporting by month for the year I should be able to add from time(0) to lastitem in January to get January levels, then the same for each successive month.
The problem I can't figure out the MDX code to do that. My basic data set is as follows:
SELECT NON EMPTY { [Measures].[Total], [Measures].[Add Remove], [Measures].[Inventory Weight] } ON COLUMNS, NON EMPTY { ([Ship From].[Ship From Region].[Ship From Region].ALLMEMBERS * [Ship From].[Ship From].[Ship From].ALLMEMBERS * [Date Received].[Date].[Date].ALLMEMBERS * [Date Received].[Year].[Year].ALLMEMBERS * [Date Received].[Month].[Month].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateReceivedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateReceivedMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@InventoryType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])))) WHERE ( IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ), IIF( STRTOSET(@InventoryType, CONSTRAINED).Count = 1, STRTOSET(@InventoryType, CONSTRAINED), [Inventory Type].[Inv Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any ideas? I've tried adding ordered sets to the query with no success. I've tried calculate measures in the cube with less success. My time dimension includes every day from 1/1/1980 to 12/31/2050.
The one calculated memeber i tried - AGGREGATE ([Date Shipped].[Date].[Date].Item(0):Tail(existing [Date Shipped].[Date].[Date]).Item(0)) took 20 minutes to run in VS Browser and produced an error for every date in the time dimension......
I have this script which pulls in all products from a product inventory with a sku range starting in 9 and ending in BK
select Product.ProductID,Product.Name,Product.SKU,ProductVariant.Inventory From Product Inner join ProductVariant on Product.ProductID=ProductVariant.ProductID where Product.SKU like '9%BK'
If I wanted to add something to this script where the inventory for all those products in the results above was changed from 0 to 20 how would I add this? Just changing those who had a a SKU starting with 9 and ending in BK
I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?
1st Design
PRODUCT TABLE ItemID ItemName Price QtyOnHand ..and other unique info of the product..
and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:
I have to caculate the fifo inventory aging from purchase date, for example I have one item with 100 units in stock, and purchase this month 20 units, and 200 units three month ago, this item has 20 units this month and 80 units from 3 month ago.
Data is as follows: <<<<< ORIGINAL DATA >>>>>>>>>>>>>>>>
I am developing an inventory database in SQL Server. I realize there aremany commercial (as well as some non-commercial) inventory offerings, but myclient has specific requirements that would necessitate significantcustomization of any off-the-shelf application. In the end, we decided itwould be more feasible to build one to our specifications.What I am looking for are a list of best practices/recommendations for thearchitecture and modeling of an inventory database. This inventoryapplication will be for managing a wholesale products operation. It mustmanage purchase orders to the manufacturer of the products, sales invoicesfor the retail customers of the products, as well as manage product stocklevels in the warehouse. We will need a number of reports, including:1. What products are in stock.2. What products are on order from the manufacturer.3. What products are needed to fulfill outstanding sales.4. Warehouse operations (e.g., receipt of delivery, inspection, add toinventory, pull from inventory, packing lists, invoices, return toinventory, etc.)5. Sales analysis (e.g., product velocity, sales by associate, etc.)My main question is regarding the managing of products in stock. As I see itthere are three ways of accomplishing this:1. Track only transactions (products received, products shipped, etc.) andcalculate the stock based on the sum of all transactions. But as transactionvolume accumulates over time, this would get very slow and cumbersome.2. Have a table of "units in stock" and add and subtract to it astransactions occur. This has the advantage of always providing an instantsnapshot of inventory levels. But it makes it more difficult to managechanges or corrections to a transaction once it has been entered.3. A combination of the above two concepts. Choose a point in time (forexample, when the warehouse inventory is audited) and capture that in a(semi) "static" table. After that, transactions are summed as needed againstthose static numbers. The next time the warehouse is audited, the "static"table is updated and all prior transactions are archived. New transactionsare once again summed against the most recent audit numbers. Of course, thiswould mean halting operations at the time of the audit (probably not anissue for my client's business).Recommendations? Suggestions? Comments?(Btw, I've tried to find this info in books and online, but so far have notfound anything that makes specific inventory system design recommendations.If you know of a good reference, please let me know.)Thanks in advance!Michael
What is a good simple way to scan the servers in your network for SQLServers?Does anyone have any scripts or code that would show me how to dothis?I think WMI might be able to do this but don't know where to start.If possible I'd like to be able to get the version (2000 or 2005) ifpossible.Thanks,Kelly GreerJoin Bytes!change nospam to yahoo
So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months. Short hand as COGS/AvgInventoryValue = Turns
In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2 Then divide AvgInventoryValue To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost) The purchased documents are represented with DOCUMENTTYPE = 6
The purchases will be positive since we are putting value in. The sales are negative since value is going out. I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.
--CREAT THE TEMP TABLE create table #VALUEENTRY ( ITEMNO VARCHAR (50) NOT NULL , POSTINGDATEDATETIME NOT NULL , DOCUMENTTYPEINT NOT NULL
[code]....
Is there another approach I should take to obtain the inventory turns?Also how do I account for periods earlier than 12 months where inventory is left over?In the sample data this item happened to be at zero inventory on hand at the start of the period. Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?
Hi everyone, I am a novice programmer. I want to develop a Inventory software using C# 2005, which will run on a LAN environment. There will be three machine which will store and retrive data from a common database. Which version of SQL server is appropiate for this ? Is it possible to provide the networking facilily using MS Access ? I don't know how to configure SQL Server for this. Please help me. Thank You.
how to measure a change in inventory over various stores. My sql2008R2 express db gets a new row of data everyday from each store(about 40 stores) for a single product stock count "OnHand" and if there is any new stock on order. When the new stock arrives it is added to the "OnHand" count. I want to measure the delta change per day,per store. I'm stuck on how to separate the stores and how to query the delta of stock.My data base looks like this
To setup some data in some new tables in t-sql 2012, I need to create a relationship between 2 tables were data is obtained from an inventory file. The inventory file will be loaded to sql server 2012 from an excel spreadsheet.
1. Columns that will be loaded to the first table are:
1. Inventory category number, 2. Inventory date, 3. Inventory Category total amount, and 4. Inventory category quantity on hand.
This table will have a column called Item number. This will need to be the value of item number that is in the second table.
2. Columns that will be loaded to the second table are:
Thus can you show me the sql that will load the table into both tables? This sql would also need to show how to obtain the values of item number from the second table and place that value into the first table.
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake FROM dbo.PartsInventory INNER JOIN dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake WHERE (dbo.RepairOrderLines.RecordID = 46001)
hi expertsss.. my msdb database is like 2gb big.. to me is really big.. so is there a way to maintain that? and how. .. also.. my disk level fragment are bad on one of my drive (some datafiles in there and msdb is there too). is there any 3rd party tool i can use to do the defragment and set schedule ? please help thanks~
My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .
i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.
Sebastian Garibaldi writes "Hi I'm Sebastian from Argentina, and i have a problem with a SQL data base. I receive error from data base of broken index and consistency errors. I set the fill factor with the information from the books online, i put 70 in tables that have a lot of INSERT/UPDATE/DELETE but it works for two o tree days.
I now that i must make some maintain in the database but which tools i shuld use?
here i paste an error from dbcc checktable:
Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 981108969. The text, ntext, or image node at page (1:949979), slot 52, text ID 57535781339136 is not referenced. Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 981108969. The text, ntext, or image node at page (1:949979), slot 53, text ID 57535782191104 is not referenced. DBCC results for 'FCRMVI'. There are 108460 rows in 17430 pages for object 'FCRMVI'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'FCRMVI' (object ID 981108969). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (ArleiProd.dbo.FCRMVI ).
I'm trying to create an inventory report for service trucks by adding all the transactions that were used to restock the truck and subtract the transactions where the parts were used on an invoice and removed from the truck. All the transactions are in the same table. The fields that would be relevant are PartID, QTY, WhsTo and WhsFrom. If I wanted to calculated stock levels for truck 16 I would select all transactions that have a value of 16 in either WhsTo or WhsFrom. If WhsTo contained 16 then I would want to add QTY. If WhsFrom value was 16 then I would want to subtract QTY. I would want it grouped by distinct PartID. I don't know how to structure the Select statement to decide whether to add or subtract.
Can anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!) Thanks. Neil.
I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.
Example tables layout: Items table has auto-increment primary key 'id' TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'
Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer: 8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'
locally the following products are added:
11001 short sleeve t---gets added with primary key in 'item' table 'id' of '7'
11002 long sleeve t----gets added with primary key in 'item' table 'id' '8'
remotely the following products are added:
21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'
31001 overalls---gets added with primary key in 'item' table 'id' '8'
remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,
product with "item_id" of '7' is "short sleeve t".
9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..
whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.
I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.
I would like to maintain version control of the all the sql objects (sp, view , tables ) and maintain source code versions control. Any way to use TFS to maintain versions of sql objects. Also the folder structure when using TFS.
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
Hey everyone, I'm new to .NET and I've recently inheirited a rather large and busy asp.net website. I was asked to add a testimonials section on each page that will randomly pull a testimonial out of the db. This is fine, however, I'm getting random errors about the DB connection either being closed or connecting. Here is the code for the testimonials class: 1 public SqlDataReader GetTestimonials(ref SqlDataReader reader, int iCatID, string sLanguageType)2 {3 SqlCommand cmd = new SqlCommand("sp_DVX_Testimonials_Fetch", Connection);4 cmd.CommandType = CommandType.StoredProcedure;5 6 cmd.Parameters.Add(new SqlParameter("@Cat_ID", iCatID));7 cmd.Parameters.Add(new SqlParameter("@LanguageType", sLanguageType));8 9 reader = cmd.ExecuteReader();10 11 return reader;12 } I know this isn't the best way to do this (especially for each page[this site averages about 1000 hits a day]), so I was wondering was--is there a way to maintain a single DB connection that's set up in the Application_Start that will maintain the connection so I don't have to worry about this error. If not, does anyone and any ideas as to what would help? Thanks in advance!
