You can Create 2 tables tblBill and tblBillDetail
tblBill will store information like customerID, BillDate or any other field that will be same for a bill.
tblBillDetail will store the Bill details like no. of items purchased (Quantiy), unit price of each item(UnitPrice), Total amount of each item purchased etc. The BillID column in table tblBillDetail links the Purchased items to the customer.
CREATE TABLE tblBill
(
BillID INT IDENTITY(1,1) CONSTRAINT PK_tblBill PRIMARY KEY,
CustomerID INT,
BillDate DATETIME
)
CREATE TABLE tblBillDetail
(
BillDetail INT IDENTITY(1,1) CONSTRAINT PK_BillDetail PRIMARY KEY,
BillID INT CONSTRAINT FK_tblBillDetail_tblBill FOREIGN KEY REFERENCES tblBill(BillID),
ProductDescription VARCHAR(100),
Quantity DECIMAL(16,2),
UnitPrice DECIMAL(16,2),
UOM VARCHAR(50),
ExtendedAmount DECIMAL(16,2),
)
Consider an example where a customer purchases 5 Tooth brushes and 2 Tooth paste.
The data will be inserted into the tables as shown below
INSERT INTO tblBill (CustomerID, BillDate) VALUES (1,GETDATE())
SELECT @BillID = SCOPE_IDENTITY()
INSERT INTO tblBillDetail (BillID, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount) VALUES (@BillID, 'Tooth Brush', 5, 3.45, 'EACH', 5 * 3.45)
INSERT INTO tblBillDetail (BillID, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount) VALUES (@BillID, 'Tooth Paste', 2,12.95, 'EACH', 2 * 12.95)
Query the two tables to retrieve the information you need.
SELECT B.BillID, CustomerID, BillDate, BillTotal, ProductDescription, Quantity, UnitPrice, UOM, ExtendedAmount FROM tblBill B
INNER JOIN tblBillDetail D ON B.BillID = D.BillID
INNER JOIN
(
SELECT BillID, SUM(ExtendedAmount) AS BillTotal FROM tblBillDetail
GROUP BY BillID
) X ON B.BillID = X.BillID