How to handle Item discount and Invoice discount levels. (1 Viewer)

[Falcon88]

Hii all dears

I have a store db ,
tblInvoic :

-InvoiceID
-InvoiceDate
-CustomerID
-DiscountAmount (Long data-type)

tblInvoiceDetails
-InvoiceNo
-ItemID
-ItemPrice
-Quantity
-ItemDiscount (Percentage) of (ItemPrice multiplied by Quantity).


some sellers offer a discount on the total value of the bill as a whole, while others give a different percentage to each item.

in the end i nead a report for :
- every Invoice .
- Monthly report .
- yearly report

How to handle this discounts?

 

[plog]

some sellers offer a discount on the total value of the bill as a whole, while others give a different percentage to each item.

Looks like you set up your tables correctly for this. If discount is on total invoice, you put it in tblInvoic.DiscountAmount; if given as percentage of specific item you put it in tblInvoiceDetails.ItemDiscount.

When you need to calculate discounts you run a subquery to add up the per item Discount (SELECT InvoiceNo, SUM(ItemPrice*Quantity*ItemDiscount) AS ItemDiscount FROM tblInvoiceDetails GROUP BY InvoiceNo). Then bring that into a query along with tblInvoic and add ItemDiscount to DiscountAmount for your total discount amount per invoice. From there you can roll it into months and years.

 

[CarlettoFed]

In order to understand how the data recorded in the tables are, you should show an example with the same, because it is not clear whether DiscountAmount and ItemDiscount are two discounted amounts or are percentage values which will allow the relative amount to be discounted.

 

[jdraw]

Falcon88,

Here is a link to an older thread describing "agreedTo" price and storing the related info in the Details record.
It may offer some insight.