inventory control system

0 hareth muthanna · November 14, 2014
hi guys 
iam working on inventory control system
i have
master table which has the items
(i can add items or delete items )
.....
example of master table
item-name item-quantity
pen 20
.....
if i add new quantities
item-name no.of added quantity
pen 4
.......
now i have 24 pen in the master table
....
all that is ok ... and i can do it with mysql
but how can i get the the( 4 ) that i added just now ?
in other words how can i get only the ( in and out ) quantities in the any day , month or year ???

Post a Reply

Replies

Oldest  Newest  Rating
0 Eugene Botma · November 14, 2014
While Jonothans ideas make sense, you will have duplicated data and unnecessary columns.

It is a better approach to have 3 different tables.

You should read up a bit about database design and normalization. Normalized databases usually seem a bit more complex, but they are actually just broken up into the most basic pieces. They're usually lightning fast and reliable.
0 Eugene Botma · November 14, 2014
I'm going to make a few assumptions here, namely:

any change in inventory is added as a new row, instead of updating existing row.
inventory in is always positive, inventory out is always negative.
you have a date of purchase or sale in the table.

to get the total current inventory for item "pen", a simple sum query will be sufficient:
select sum(item-quantity) as item-inventory from item where item-name = 'pen'

if you want to get the total for all items, remove the where and group the query by item-name


If you want it for a certain time span (like a day, month, year, etc)
select sum(item-quantity) as item-inventory from item where item-name = 'pen' and date>='inclusive start date' and date <= 'inclusive end date'

you can use > and < to make the start and end dates exclusive instead. As above, you can use a group by clause to select all items, just remove the item-name check.

To get the total in or total out, add "and item_quantity>0" for in or "and item_quantity<0" for out to the where clause.

you can create nested queries for getting all in and all out in one query, for example:
select (select sum([item-quantity]) from item where [item-name]='pen' and [item-quantity]>0) as [item-in],
(select sum([item-quantity]) from item where [item-name]='pen' and [item-quantity]<0) as [item-out] 
0 hareth muthanna · November 14, 2014
thank you very very much
this was very helpful
...
know iam thinking that i will make 3 tables 
master table
in  table
out table
to help me get it easily 
0 Jonathan Hickey · November 14, 2014
In your table you will need to have a DATETIME column and a IN_OUT column.

The DateTime will help you find between two dates which pacific times should not allow NULL values. The In_Out column should only accept 2 values 'IN' and 'OUT' also not allowing NULL values. You may want to have a third value for that column 'Ordered'

table would look like.

primary_ID | Item | Amount | IN_Out | Date_Time 

Primary_ID helps you cluster your table making your table faster. 

your query would look like:

--returns amount of pens in
Select distinct Item, sum(amount) from table
where item = 'Pen'
and In_Out = 'IN'

--returns amount of pens out
Select distinct Item, sum(amount) from table
where item = 'Pen' 
and In_Out = 'OUT'

--return amount of pens in and out between 2 dates

select * from 
(select distinct item, sum(Amount) as 'IN', from table
where item = 'Pen'
and In_Out = 'IN'
and date_Time between '2014-11-10 00:00:00.000' and '2014-11-10 23:59:59.999') as A
join 
(Select distinct Item, sum(amount) as 'Out' from table
where item = 'Pen' 
and In_Out = 'OUT'
and date_Time between '2014-11-10 00:00:00.000' and '2014-11-10 23:59:59.999') as B
on a.item = b.item

Hope that works with out testing it.. But I am sure you get the idea.

Hope that helps.
0 hareth muthanna · November 14, 2014
thank you Jonathan 
you and Eugene Botma 
helped me so much
i will continue to work on my project ... i hope it will work 
and if i have any questions i will post them here :)
thank you very much ...
  • 1

SQL & Databases

106,948 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator