Solved

SQL Ordered Products with Discount Codes

Posted on 2016-08-24
3
31 Views
Last Modified: 2016-08-24
Hello,

We have these tables in a SQL 2008 database:  

- Orders
- OrderedProducts
- Discounts

Orders and OrderedProducts have a relationship of one to many. OrderedProducts contains all the products from each order. In the OrderedProducts table there is a field where a discount code can be entered.

The Discounts table has a series of records that consist of the ProductName and the DiscountCode. Usually, there is a unique DiscountCode per each ProductName but if we have promotions where they need to buy 2 or 3 products in the same order, they will share the same DiscountCode.

This is an example of how records in the DiscountCode table would look like

ID      Product          DiscountCode
11      WidgetA            TwENTYOFF
12      WidgetB            FIFTEENOFF
13      WidgetC            TwENTYOFF
14      WidgetD            TEN0FF


And this would be some orders


Orders
OrderID      EntryDate      Purchaser
2929      8/12/16            Jane Doe
2930      8/13/16            John Smith
2931      8/15/16            Jay Lee


And here are their Ordered Products with their discounts (note OPID 4285 has a wrong discount because someone tried to use a better, but wrong discount for this product).

OrderedProducts
OPID      OrderID      Product        DiscountCode
4283      2929      WidgetA            TwENTYOFF
4284      2929      WidgetC            TwENTYOFF
4285      2930      WidgetD            TwENTYOFF
4286      2931      WidgetB            FIFTEENOFF

How can I get a list of orders containing records where users entered the correct discount and all products were purchased for that discount code?  For example, this list should contain
the following:

OrderID      EntryDate      Purchaser      Product      DiscountCode
2929      8/12/16            Jane Doe              WidgetA            TWENTYOFF
2929      8/12/16            Jane Doe         WidgetC            TWENTYOFF
2931      8/15/16            Jay Lee              WidgetB            FIFTEENOFF


Order2930 should not be included because even though John Smith entered a product code, it was the wrong one.  I also expect this records to show multiple lines if a DiscountCode has more than one product that needs to be purchased with this code like the first two records.  


Thanks.
0
Comment
Question by:TheUndecider
3 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 250 total points
ID: 41769323
DECLARE @Discount TABLE
(
	ID				INT,
	Product			VARCHAR(50),
	DiscountCode	VARCHAR(50)
);

INSERT @Discount (ID, Product, DiscountCode)
VALUES
	(11, 'WidgetA', 'TwENTYOFF'),
	(12, 'WidgetB', 'FIFTEENOFF'),
	(13, 'WidgetC', 'TwENTYOFF'),
	(14, 'WidgetD', 'TEN0FF');

DECLARE @Order TABLE
(
	OrderID			INT,
	EntryDate		DATETIME,
	Purchaser		VARCHAR(50)
);

INSERT @Order (OrderID, EntryDate, Purchaser)
VALUES
	(2929, '8/12/16', 'Jane Doe'),
	(2930, '8/13/16', 'John Smith'),
	(2931, '8/15/16', 'Jay Lee');

DECLARE @OrderProduct TABLE
(
	OPID			INT,
	OrderID			INT,
	Product			VARCHAR(50),
	DiscountCode	VARCHAR(50)
);

INSERT @OrderProduct (OPID, OrderID, Product, DiscountCode)
VALUES
	(4283, 2929, 'WidgetA', 'TwENTYOFF'),
	(4284, 2929, 'WidgetC', 'TwENTYOFF'),
	(4285, 2930, 'WidgetD', 'TwENTYOFF'),
	(4286, 2931, 'WidgetB', 'FIFTEENOFF');

WITH cteDiscountOrder AS
(
	SELECT O.OrderID
	FROM @Order AS O
	INNER JOIN @OrderProduct AS OP
		ON O.OrderID = OP.OrderID
	LEFT OUTER JOIN @Discount AS D
		ON OP.Product = D.Product
		AND OP.DiscountCode = D.DiscountCode
	GROUP BY O.OrderID
	HAVING COUNT(O.OrderID) = COUNT(D.ID)
)
SELECT O.*, OP.*
FROM cteDiscountOrder AS cte
INNER JOIN @Order AS O
	ON cte.OrderID = O.OrderID
INNER JOIN @OrderProduct AS OP
	ON O.OrderID = OP.OrderID

Open in new window

0
 
LVL 1

Assisted Solution

by:Helen Ramsden
Helen Ramsden earned 125 total points
ID: 41769325
Hi, does this help?

SELECT dbo.Orders.OrderID, dbo.Orders.EntryDate, dbo.Orders.Purchaser, dbo.OrderedProducts.Product, dbo.OrderedProducts.DiscountCode
FROM     dbo.Orders INNER JOIN
                  dbo.OrderedProducts ON dbo.Orders.OrderID = dbo.OrderedProducts.OrderID
WHERE  (dbo.OrderedProducts.Product + dbo.OrderedProducts.DiscountCode) IN
                      (SELECT Product + DiscountCode
                       FROM      dbo.DiscountCode)
0
 
LVL 10

Assisted Solution

by:Marcjev
Marcjev earned 125 total points
ID: 41769345
select O.OrderId, O.EntryDate, O.Purchaser, P.Product, p.DiscountCode, *
from orders O
join OrderedProducts P on p.OrderId = O.OrderId
join (
	select	P.DiscountCode, count(*) NumberOfProducts	
	from orders O
	join OrderedProducts P on p.OrderId = O.OrderId
	join Discounts C on C.DiscountCode = P.DiscountCode and C.Product = P.Product
	group by p.DiscountCode ) as no on no.DiscountCode = p.DiscountCode
join (
		select discounts.DiscountCode, Discounts.id, Discounts.Product, no.NumberOfProducs 
		from discounts 
		join ( 
				select discountcode, count(*) NumberOfProducs 
				from Discounts 
				group by discountcode
			) as no on no.DiscountCode = discounts.DiscountCode
	) as no2 on no2.product = P.product and no2.DiscountCode = p.DiscountCode and no.NumberOfProducts = no2.NumberOfProducs

Open in new window


Remove the  ,* to see the output you requested.

(also try 2 times selling widgetA with correct discountcode but no widgetC. Should it not showup as correct orders?)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now