Inventory Report


My database with the following table:
ITDetail Table:

InvTrans Table:

Currency Table:

Database Relationship:



I need to build a report with the time parameter (use the procedure) with the following requirements:



MaterialID, Art, Color, Width is taken from the table ITDetail
Period: 1 to 90 (days)
a. Quantity: with TypeID is 'X' is the OUT quantity (X001, X002, ...), with TypeID is 'N' is the INT quantity (N021, N014, ...)
A: Total quantity IN of 1 to 91 (days)
B: Total quantity OUT of 1 to 91 (days)
C: The quantity need to find

If: A≥B then C = A B; If A
Posted On: Monday 31st of December 2012 01:45:25 AM Total Views:  358
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




inventory database

I have a charity that collects used sports gear for needy kids. I need to track the type and amount of gear we receive. Here's the question- How can I track the quantity of a specific item AND where that item came from. Example: 5 jerseys received from store A 7 jerseys received from store B 20 jerseys received from store C I need to be able to say we received 32 jerseys total. I also need to break it down by source... store A has donated 5 jerseys. I will use MySQL and phpMyAdmin.
VIEWS ON THIS POST

70

Posted on:

Thursday 25th October 2012
View Replies!

Need report like below

i have table like this, |-------------------------------------------------------------------| CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `merchant` varchar(255) NOT NULL default '', `name` varchar(255) NOT NULL default '', `description` text NOT NULL, `image_url` varchar(255) NOT NULL default '', `buy_url` text NOT NULL, `price` decimal(10,2) NOT NULL default '0.00', `search_name` varchar(255) NOT NULL default '', `category` varchar(255) NOT NULL default '', `brand` varchar(255) NOT NULL default '', `dupe_hash` varchar(32) NOT NULL default '', `rating` int(11) NOT NULL default '0', `reviews` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `dupe_filter` (`dupe_hash`), KEY `merchant` (`merchant`), KEY `name` (`name`), KEY `search_name` (`search_name`), KEY `category` (`category`), KEY `brand` (`brand`), FULLTEXT KEY `name_2` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=32185 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32185 ; |-------------------------------------------------------------------| i need to display report as follows,( i need first sort by merchant & second brand) Merchant | Brand | No. Of products( related to merchant & brand) please reply soon,
VIEWS ON THIS POST

109

Posted on:

Thursday 25th October 2012
View Replies!

Need to create a report from 2 Tables but getting Error messages

, I need to create a report looking at 2 different Tables. Lets say table 1 is called "support_ticket", this table contains the main info about a new support ticket, such as: Author, Date_Posted, Author_ID, Address , etc. Table 2 contains the messages posted and replies posted to the messages, it is called table "replies" and it contains info such as: Reply_Author, Reply_Message, Reply_date, Reply_to_what So here is what I need to do: create a report which lists those new Tickets that have been opened which have not been answered I tried this but it is not working: SELECT id, LEFT(subject, 40) AS mini_subject , author, status, date_added FROM support_ticket, replies WHERE support_ticket.id = replies.support_id AND MAX(reply_date) IS NULL GROUP BY id; Your help is greatly appreciated.
VIEWS ON THIS POST

170

Posted on:

Thursday 25th October 2012
View Replies!

Any db design of FIFO for inventory control

I am asked to do a job that's creating fifo (first in first out) for inventory control. There are many kinds of transaction such as: Goods purchasing, Return Goods Purchased, Moving Goods from one warehouse to another one, change the goods name (like from fresh tomatoes to rotten tomatoes), Selling (Invoice) and Credit Invoice (Return for Sales). I was so confused and I did a table like this: Code: CREATE TABLE IF NOT EXISTS `fifotrans` ( `id` int(11) NOT NULL auto_increment, `fifotransferid` int(11) NOT NULL default '0' COMMENT 'this trans is from another fifo transaction if 0', `transid` int(11) NOT NULL, `bbmid` int(11) default '0', `transtype` varchar(10) NOT NULL, `transsigned` tinyint(4) NOT NULL default '1', `transdate` date NOT NULL, `goodsid` int(11) NOT NULL, `whouseid` int(11) NOT NULL, `qty` int(20) NOT NULL default '0', `remain_qty` int(11) NOT NULL default '0', `remain_cost` bigint(20) NOT NULL default '0', `cost` bigint(20) NOT NULL default '0', `userid` varchar(11) NOT NULL, PRIMARY KEY (`id`), KEY `goodsid` (`goodsid`), KEY `whouseid` (`whouseid`), KEY `fifotransferid` (`fifotransferid`), KEY `fifotransferid_2` (`fifotransferid`), KEY `transdate` (`transdate`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='transaction fifo' AUTO_INCREMENT=1 ; explaination: the fifotransferid refers to this table but on different records. This will be signed with id. For example, if the goods was moved from one warehouse, this fifotransferid will refer to which record the goods was come from. transtype -> 'invoice', 'ci', 'returngoods', 'purchasing', 'trx_in', 'trx_out', etc transsigned -> 1 = in , 0 = out bbmid = from goods purchasing so that i can track where this goods comes from. is my database table was correct i would like to see another table design for this Fifo Inventory control if you have one ...
VIEWS ON THIS POST

209

Posted on:

Thursday 25th October 2012
View Replies!

optimising reporting performance on large datasets

This is kind of a general question. Say you've got a fairly large and contually updated dataset. For example a record of page visitors to you site, or orders you take. I'm finding that running queries on such large datasets is taking longer and longer. I'm satisfied that the queries and indexes are fine, it's just the volume of data that is slowing things up, and my concern is that running my queries is hogging database & server resources. What kind of approach would people suggest for managing large datasets I thought about holding a regularly updated copy of the data on another database or another server, dedicated to running reports. Or running a script to summarise data. For example into a table holding number and amounts of orders by date. Thoughts
VIEWS ON THIS POST

83

Posted on:

Thursday 25th October 2012
View Replies!

JOIN only the most recent record

I am wanting to show the most recently updated topics (and post/user info) for a forum in a single query. Somthing along the lines of: Code: SELECT * FROM topics LEFT JOIN posts ON topics.topicID=posts.postID GROUP BY topics.topicID OR Code: SELECT topics.topicID, MAX(postDate), postBody FROM topics LEFT JOIN posts ON topics.topicID=posts.topicID GROUP BY topics.topicID But the oldest post is joined instead of the most recent post. I've tried this also but this starts to lagg when I need the postBody, postDate, postAuthor etc: Code: SELECT topics.topicID ( SELECT postDate FROM posts WHERE topics.topicID=posts.topicID ORDER BY postDate DESC LIMIT 1) as postDate FROM topics LEFT JOIN posts ON topics.topicID=posts.topicID GROUP BY topics.topicID I read somewhere about checking through the posts for a topicID until there is no postDate larger than the current ... something like this (I know this makes no sense btw): Code: SELECT * FROM topics LEFT JOIN postts ON posts.topicID=topics.topicID AND postDate
VIEWS ON THIS POST

64

Posted on:

Monday 29th October 2012
View Replies!

Convert military time to standard AM/PM time?

I have a date function in the format 00:00:00 so basically it stores time as miltary format. Now, I have the time 22:00:00 and when extract it from the database, I want to format/output 10:00 PM, how would I do that
VIEWS ON THIS POST

73

Posted on:

Monday 29th October 2012
View Replies!

Optimizing my database design

I am currently managing a social networking website which has a few hundred thousand members, and I am starting to run into some problems with the load created by the search features on the website. Right now, the users' table contains a good deal of data for each user on the site, and we allow people to make searches for users using various criterias. of the criterias (like male or female, age, etc.) have their own columns in that table, with most columns being tinyints or ints. I have added indexes to pretty much every individual column that can be used as a search criteria. The current design is still working, but it is starting to create a big load on the database, and this will only get worse as the site continues to add members. Unfortunately, the fastest way to perform the searches would be to have a single index that covers all the criterias instead of a multitude of indexes being merged together by mysql dynamically. But this solution does not work with how our searches are being done, since every criteria is optional. So if I was to do an index like 'sex,age,height,weight,etc', this index would be useless if someone performed a search that does not involve gender, or a search that does not involve age, etc. After a lot of research, the only alternative solution I came up with was to create a SET column, which would hold a bunch of different data about the user (male, female, tall, average_height, short, big, average_weight, small, etc.) and then run the search query against this single column with the find_in_set function. Problem with this solution is that I am restricting myself to 64 possible informations, which I guess could cover the various search criterias we have available right now, but would not allow for much growth in the future if we want to add new criterias. From the tests I ran with a dummy table filled with random data, this setup would perform the searches much faster than the current setup (and still run fine with even 3 million entries to search through), but I just have a nagging feeling that this is not the best solution available. It also creates the problem of keeping duplicate information in the database and managing this duplicate information when members update their profile, etc. But a 64 bit column on a few hundred thousand records is only a few MBs of disk space, so it is not that big of a deal. Does anyone know of alternative solutions I could use to search efficiently through a big table using a variety of different rows
VIEWS ON THIS POST

87

Posted on:

Monday 29th October 2012
View Replies!

Learning how to write Stored Procedures

Originally posted by : Jeff Coon Does anyone have a good tutorial for learning about stored procedures I've gone through some tutorials on how to write SQL statements, and I'm doing pretty well in that respect. But now I want to move all of my SQL statements in ASP pages into stored procedures. Any good links - Jeff
VIEWS ON THIS POST

70

Posted on:

Wednesday 7th November 2012
View Replies!

Differences Between SQL Server & Oracle SQL

Originally posted by : Martin Burford (martin_burford@hotmail.com),Does anybody know of any resources available on the internet that details the differences between Oracle SQL and Microsoft SQL Server. I am currently creating ASP applications linking to Oracle Databases, and I need to know the syntax differences when sending/retrieving data from tables when using SQL Server databasesCheers for your helpMartin
VIEWS ON THIS POST

37

Posted on:

Wednesday 7th November 2012
View Replies!

Multiple table updates with SQL and ADO

Originally posted by : Mark (question@paratrooper.net)Ok, trying to use ADO (addnew) to insert into 2 tables (joined). I've separated the data and called an rs.update after each table but I keep getting.Microsoft OLE DB Provider for SQL Server error '80004005' Cannot insert or update columns from multiple tables. If anyone has info or a link to an online tutorial on inserting/updating multiple tables that are joined via ADO, please email me and let me know.
VIEWS ON THIS POST

76

Posted on:

Saturday 10th November 2012
View Replies!

[Query] Multiple counts from same table

I have an Access database with the following table in it: Code: OrderID ItemID ShopID 1 1 1 2 3 1 3 5 2 4 3 1 5 3 1 I am trying to get the count of 3 different things, using one query. Those being: The number of ordersThe number of different items ordersThe number of shops ordered from So, using the above example I would get the following results: NumberOfOrders = 5 NumberOfItems = 3 NumberOfShops = 2 I know how to achieve each of them: Code: Number of orders: SELECT Count(*) As NumberOfOrders FROM Orders Number of Items: SELECT Count(ItemCount) As NumberOfItems FROM (SELECT Count(ItemID) As ItemCount FROM Orders group by ItemID) Number of Shops: SELECT Count(ShopCount) As NumberOfShops FROM (SELECT Count(ShopID) As ShopCount FROM Orders group by ShopID) I just don't know how to concatenate all of that into one query. I could do them seperately, but I don't want to be making 3 calls to the database, when I'm sure it can be done in one. Hopefully that makes sense.
VIEWS ON THIS POST

75

Posted on:

Tuesday 20th November 2012
View Replies!

Accessing the database (excel)

Originally posted by : cassandra (limbeehar77@yahoo.com),I encountered some problems when trying to load the page."Provider error '80004005' Unspecified error /fyp1.asp, line 26 "Line 26: cnnExcel.Open "DBQ=" & "e:InetPubwwwroot emp.xls" & ";" & _ "DRIVER={Microsoft Excel Driver (*.xls)};"orLine 26: cnnExcel.Open "DBQ=" & Server.MapPath("temp.xls") & ";" & _ "DRIVER={Microsoft Excel Driver (*.xls)};"i have tried both method but i still received the error message. Pls help.
VIEWS ON THIS POST

121

Posted on:

Tuesday 20th November 2012
View Replies!

I have no idea where to begin

I'm designing a website for people who ride motorcycles. When each person registers, they must first enter what model of motorcycle they ride. What I would like to do is, in each persons user profile, show them a list of all the other people who have the same model motorycle as they do. I have no idea where to begin with the sql on this one...
VIEWS ON THIS POST

67

Posted on:

Tuesday 20th November 2012
View Replies!