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
/*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
*/
-
I see you took my advice and reposted to the 2012 forum with the DDL. Good for you!
Now a few questions:
- Can a transaction qualify for the same event more than once?
- Do you allocate sales to the GroupIDs with the larger quantities first or the smaller quantities first?
- I am also unclear about the difference between GrpMinQty/GrpMaxQty and GrpSkuMinQty/GrpSkuMaxQty. Can you explain?
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 -
While I don't have time at the moment to craft a full-blown solution for you, I might have a couple of ideas to get you started.
I don't think you're going to be able to do this without some kind of a loop. So you might want to start with something like this:
SELECT TransactionNumber, SkuNumber, QuantitySold=SUM(QuantitySold)
,ProductAllocated=0
INTO #Temp
FROM SalesTransactionDetail
GROUP BY TransactionNumber, SkuNumber;
Then go into a set-based WHILE loop, which checks for each EventID (one at a time) whether each transaction qualifies based on QuantitySold - ProductAllocated (to an event). You would then need to update ProductAllocated (in #Temp) for each SKU that qualified for an event. Finally loop back and check the next event.
The "set-based" nature of the WHILE loop is to indicate you can process all transactions that remain with unallocated products on each pass through the loop.
Easier said than done (probably).
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 -
Yes, transaction can qualify more than once. Quantities are allocated to the groups that yield the most $ amount.
The quantities mean the same thing.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply