Customer Purchase Incentive Validation in T-SQL
-
Hello all - I would like your input on how to address this problem using T-SQL. I don't expect you to do this for me, but I would appreciate at least some guidance on how to solve this problem using T-SQL.
I'm tasked to validate sales transactions and find out the Events the transactions qualifies for base on the products on the transaction detail.
Here are the requirements:
•We use 'SkuNumber' to refer to the products. The SKuNumbers are grouped into SKU Groups.
•The SKU Groups are used in Events. An Event will have at least one SKU Group; when the Event has more than 1 SKU Group, it is called a Bundled Event.
•A SKU Group can be part of multiple Events.
•Each Group will have a MinQty and MaxQty of SkuNumbers to purchase in order to qualify for the Event.
•Any particular transaction can qualify for more than 1 Event if they purchased enough SKUs to apply to the other Event (keep in mind the min and max requirements for the SKUs for each Event).
The Events are configured beforehand with the SKU Groups in a lookup table.
The lookup table for SKU Groups is as follows:
Here is an example of how we set up the Events:
Event A: Customer gets $2 with the purchase of 1 product from Group 10581 and 1 product from Group 10582
Event B: Customer gets $2 with the purchase of 1 product from Group 10581 and 5 product from Group 10583
My data comes from a sales transaction feed that has the SKUNumber and the Quantity purchased for each SKuNumber
-
Admin, please move this post to the TSQL 2012 forum. I just realized that it was posted in 2K5.
-
SQLSig (11/5/2013)
Admin, please move this post to the TSQL 2012 forum. I just realized that it was posted in 2K5.Unfortunately, I don't believe this forum has an administrator that will move your post to the 2012 forum. Neither can you close a thread.
What you can do is to re-post to the SQL 2012 forum, and post a notice here to refer to that thread (use the url IFCode Shortcut on the left side of the posting window to post the URL).
If you do so, then you're going to need to help us (free) helpers out a bit by providing
- DDL for the tables you've presented
- Consumable sample data in the form of INSERTs into those tables
If the above matches the pictorial data you provided, the expected results are clear. So are the requirements. The only challenging requirement in the lot is the last one. And you may want to add some sample data and expected results to cover that one (I don't think it is covered in what you provided).
I think if you follow my suggestion someone will come along and give you a working solution pretty quickly.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
/*Here is the DDL and sample data. */
--Create and Insert for EventSkuGroup
CREATE TABLE [dbo].[EventSkuGroup](
[EventID] [int] NOT NULL,
[GroupID] [int] NOT NULL,
[SKUNumber] [int] NOT NULL,
[GroupMinQty] [int] NOT NULL,
[GroupMaxQty] [int] NOT NULL,
CONSTRAINT [PK_EventSkuGroup] PRIMARY KEY CLUSTERED ([EventID] ASC,[GroupID] ASC,[SKUNumber] ASC)
)
GO
INSERT [dbo].[EventSkuGroup] ([EventID], [GroupID], [SKUNumber], [GroupMinQty], [GroupMaxQty])
VALUES
(1019767, 10583, 90054574, 5, 5)
, (1019767, 10583, 90054575, 5, 5)
, (1019767, 10583, 90054576, 5, 5)
, (1019767, 10583, 90054577, 5, 5)
, (1019767, 10583, 90054578, 5, 5)
, (1019767, 10581, 80054574, 1, 1)
, (1019767, 10581, 80054575, 1, 1)
, (1019767, 10581, 80054576, 1, 1)
, (1019767, 10581, 80054577, 1, 1)
, (1019769, 10581, 80054574, 1, 1)
, (1019769, 10581, 80054575, 1, 1)
, (1019769, 10581, 80054576, 1, 1)
, (1019769, 10581, 80054577, 1, 1)
, (1019769, 10582, 70054574, 1, 1)
, (1019769, 10582, 70054575, 1, 1)
, (1019769, 10582, 70054576, 1, 1)
, (1019769, 10582, 70054577, 1, 1)
GO
--Create and insert for Event setup
CREATE TABLE dbo.EventItem
(
EventID INT not null,
GroupID INT not null,
GroupSkuMinQty INT not null,
GroupSkuMaxQty int not null,
ItemAmount numeric(5,0) not null
)
GO
INSERT [dbo].[EventItem] ([EventID], [GroupID], [GroupSkuMinQty], [GroupSkuMaxQty], [ItemAmount])
VALUES (1019767, 10581, 1, 1, CAST(1 AS Numeric(5, 0)))
,(1019767, 10583, 5, 5, CAST(1 AS Numeric(5, 0)))
,(1019769, 10582, 1, 1, CAST(1 AS Numeric(5, 0)))
,(1019769, 10581, 1, 1, CAST(1 AS Numeric(5, 0)))
GO
--Create and Insert for Sales Feed
CREATE TABLE dbo.SalesTransactionDetail
(
TransactionNumber INT not null,
LineNumber INT not null,
SkuNumber INT not null,
QuantitySold int not null,
CONSTRAINT FK_SalesTrankey PRIMARY KEY CLUSTERED (TransactionNumber ASC,LineNumber ASC)
)
GO
INSERT [dbo].[SalesTransactionDetail] ([TransactionNumber], [LineNumber], [SkuNumber], [QuantitySold])
VALUES (101, 1, 90054574, 1)
,(101, 2, 80054577, 1)
,(101, 3, 90054574, 1)
,(101, 4, 70054575, 1)
,(101, 5, 90054574, 1)
,(101, 6, 90054578, 1)
,(101, 7, 90054574, 1)
/*
Based on the event setup, the output should be:
Customer qualifies for Event 1019767 only because customer purchased 5 from group 10583 and 1 from group 10581. See EventItem for this setup.
Customer does not qualify for Event 1019769 because this event requires purchase of any 1 SKU from group 10581 and any 1 SKU from group 10582, but the customer only purchased 1 SKU 8005457 from group 10581, but this quantity was already applied to Event 1019767. In other words, the two Events share a SKU group (10581) and the customer only purchased SKU 80054577 with a quantity of 1; there is not enough to apply to the second event. This would have been ok if the customer had also purchased another SKU from group 10581 or if the customer had purchased 2 or more of any SKU from group 10581. We would end up with the customer qualifying for both events.
Hope this helps. Thanks!
EventID GroupID SKUNumber QuantityPurchased
1019767 10583 90054574 4
1019767 10583 90054578 1
1019767 10581 80054577 1
*/
-
This topic was re-posted in the TSQL 2012 forum.
Please use this link to the new location: http://www.sqlservercentral.com/Forums/Topic1511715-3077-1.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply