SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Customer Purchase Incentive Validation in T-SQL


Customer Purchase Incentive Validation in T-SQL

Author
Message
SQLSig
SQLSig
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 192
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

SQLSig
SQLSig
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 192
Admin, please move this post to the TSQL 2012 forum. I just realized that it was posted in 2K5.
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7569 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
SQLSig
SQLSig
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 192


/*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
*/


SQLSig
SQLSig
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 192
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search