Invoice Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Invoice Table

Author

Topic

websyd
Starting Member

16 Posts

Posted - 2006-11-08 : 04:43:48

Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table

Please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 05:08:34

I think you have to provide more information. Sample data and expected output based on the provided sample data would be great.

Or are you asking us how to build your system?

Peter Larsson
Helsingborg, Sweden

websyd
Starting Member

16 Posts

Posted - 2006-11-08 : 07:47:47

Thanks 4 d reply but im not asking that u should build one 4 me, just trying to get an idea how my application will be able to generate an invoice based on the quantity in stock and this quantity is in the delivaries table. Im a newbe

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-08 : 09:01:02

quote:
Originally posted by websyd
I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table

Well you have your requirements right there...and that's derived data

What you would be better off doing is to create a view based on these reqs...BUT

Sample Data, DDL ect would help us help you

Read the hint link in my sig and post what asks...you should have an answer in, oh, 5 minutes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-11-08 : 09:54:10

"I will like to create ... Invoice Table"

We use two tables:

Invoice Header Table
====================
Ref No
Customer ID
Reference to Invoice Address
Reference to Delivery Address
Date
Status (Invoiced, Paid, Cancelled, etc.)

We also store:
Tax Method
Shipping method
Shipping cost
Tax cost
Goods cost
Total cost (costs are to avoid having to calculate them in different places)
Promotional Voucher(s)
Customer Reference No
Batch No
Payment Method
Gift flag / Message
Date customer wants delivery
Early Payment Discount
Affiliate Ref
and a bunch of other stuff

Invoice Item Table
==================
Ref No (FK to above)
Item
Product Code
Qty
Price

We also store:
Status
Discount Price
Qty Shipped
Qty Cancelled
Tax Code
Tax Amount
Goods Amount
Total Amount
and a bunch of other stuff.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources