Solved

SQL Ordered Products with Discount Codes

Posted on 2016-08-24
3
40 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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