June 20, 2011 at 3:13 am
Hi all,
I am going to design an inventory system of Different type of Notebooks. Consider this scenario:
If i bought 100 notebooks of type "NB001" on "10-Jan-2011", then I sold 5 "NB001" notebooks on "1-Feb-2011". So Closing stock is "95" on Feb month end. Like this its going on. I need the inventory report as shown in the image.
Please refer the attached image. Month end "closing stock" becomes next months "Opening Stock". I need a simple table structure to track this and achieve this output. Pls help to design the db in this regard.
P.S.
Please consider for the above input "From Date" is "1-Jan-2011" and end date is "30-Apr-2011".
And also consider the table entries are put from "1-Jan-2011" onwards. So in the image, For Jan month the opeing stock is "0".
Thanks
June 20, 2011 at 6:51 am
If you've attempted a design and failed, please post the DDL code and data sample so we can point out where your errors are. We'll be happy to help you with any problems you have.
If you are expecting us to do your data design for you, we don't do that. Not unless you pay us. Going rate is $100.00 an hour for me (with an 8 hour minimum charge). Others charge much more.
To learn how to create tables yourself, get a copy of Books Online and go through the tutorials. They're not as scary as they look and you'll learn a lot.
Lastly, remember that report design is not the same thing as database design. Two separate animals. Reports are meant to look pretty. Database schema is meant to be functional.
June 20, 2011 at 12:11 pm
Thank you for your reply brandie. Take this sample script and help me out to get the result same as in my prev post image.
CREATE TABLE [dbo].[tblProduct](
[i_id] [int] NOT NULL,
[vc_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[vc_desc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblInventory](
[i_inventoryId] [int] NOT NULL,
[i_productid] [int] NOT NULL,
[dt_buydate] [datetime] NOT NULL,
[dt_soldDate] [datetime] NOT NULL,
[i_buy_qty] [int] NOT NULL,
[i_sold_qty] [int] NOT NULL
) ON [PRIMARY]
insert into tblProduct values(1,'NB-001','NoteBook1')
insert into tblProduct values(2,'NB-002','NoteBook2')
insert into tblProduct values(3,'NB-003','NoteBook3')
insert into tblProduct values(4,'NB-004','NoteBook4')
insert into tblProduct values(5,'NB-005','NoteBook5')
insert into tblInventory values(1,'2','1-Jan-2011','10-Jun-2011',100,10)
insert into tblInventory values(2,'1','10-Feb-2011','15-May-2011',50,15)
insert into tblInventory values(3,'2','5-Jan-2011','16-Apr-2011',10,20)
insert into tblInventory values(4,'1','22-Mar-2011','25-Mar-2011',100,10)
insert into tblInventory values(5,'2','17-Apr-2011','10-May-2011',100,0)
Thanks.
Thank you.
June 20, 2011 at 12:15 pm
What queries have you tried based on the above table schema?
June 21, 2011 at 4:35 pm
francissvk (6/20/2011)
I need a simple table structure to track this and achieve this output. Pls help to design the db in this regard.
Allow me to show you how to fish - instead of giving you a fish. In short, how to approach Data Modeling.
Let's assume the information that can be infered from the report you attached are all the business requirements for this particular project.
On that assumption, please go ahead prepare and post an ER Model - short for Entity-Relationship Model - describing your project. Just to give you a jump-start let me list two Entities and one Relationship.
Entity #1 - Products
Entity #2 - Customers
Relationship #1 - Products are sold to Customers.
After having a sound ER Model we can move to actual table design.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2011 at 8:14 am
I think, from the lack of reply, that this may have been a homework project. Either that or the OP didn't actually do any design / code beyond the table structure he posted.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply