Ignore duplicates?

[shophoney]

Hi I have a situation with my sales tables.

Everything was working fine till I found out that some items have 2 discounts applied to one item. This causes my system to look like the item was sold twice.

LOC#	TRANS DATE	TIME	TRANS#	CUST#	salesperson	SKU#	UNITS SOLD	COST	DISCOUNT CODE	disc_source	DISCOUNT	NET RETAIL	line_no
3000	02/06/16 12:00:00 AM	12/30/99 3:53:52 PM	67104	200000BNN	VUKB	1124616	1	$118.25	20%	DO	-$27.50	$110.00	1
3000	02/06/16 12:00:00 AM	12/30/99 3:53:52 PM	67104	200000BNN	VUKB	1124616	1	$118.25	50P121515	BO	-$137.50	$110.00	1

<tbody>
</tbody>

From above the item was sold once but received 2 discounts. One 20% off and the other was "50P121515"

Line#1 is the item sold. And should be one unit in total sold. But as there was 2 types of discounts used it lists units 2x.

I hope that is clear. Maybe i need to total units sold based on unique line numbers.

 

[MarcelBeug]

It depends on your business logic. If 2 discounts are allowed then you need a separate table with the discounts per order line.
Otherwise you need some logic to select the 1 applicable discount, so you won't have multiple discounts per line.

If it is only about calculating the number sof items sold, then you can first remove duplicate combinations as you suggested.
But I wouldn't recommend it; you should prevent duplicate records in your Sales table.