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