Solved

SQL Ordered Products with Discount Codes

Posted on 2016-08-24
3
53 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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