Hi Mark,
What an excellent reply. Thanks a ton. It just lacks one thing and that's because I dint explained that case in my requirement but you asked that question in your reply. Please let me know how to solve that.
Regarding your question:
If you had the case where two mappings, mapping 1 (ABC) and mapping 2 (BCD), which would you require for an order with products ABCD as both would match.
Ans: In this case in the mapping table will have a mapping for D,B,C ->T and the output should have the result with both the combinations. One for the output for ABC and one for the output BCD. An order can contain products alone , product with B or product with B and C and mapping table would have mapping for all the scenarios. In my expected output I showed a line as 1 | Z | 2 | 200 but this was a mistake from my end. Since order 1 had also B and C in it, the output should retrieve the corresponding output for the combination of (DBC) i.e. T and not for D alone.
I have added the changes in Mapping_IN and Mapping_OUT table with the OUTPUT for DBC but its not giving output T in the result.
declare @Order table
(OrderID int,
OrderItem varchar(10),
Quantity int,
Price money)
declare @Mapping_IN table
(MapID varchar(3),
OrderItem varchar(10),
MajorProduct int)
declare @Mapping_OUT table
(MapID varchar(3),
OrderItem varchar(10))
insert into @Order values
(1,'A',3,960),
(1,'B',1,0),
(1,'C',1,0),
(1,'D',2,200),
(2,'E',5,100),
(2,'B',1,0),
(2,'C',1,0),
(3,'Q',6,1000),
(4,'B',1,0),
(4,'A',3,300),
(5,'A',7,4000)
insert into @Mapping_IN values
('S1','A',1),
('S1','B',0),
('S1','C',0),
('S2','A',1),
('S2','B',0),
('S3','A',1),
('S4','D',1),
('S5','E',1),
('S5','B',0),
('S5','C',0),
('S6','Q',1),
('S7','J',1),
('S7','B',0),
('S7','C',0),
('S8','D',1),
('S8','B',0),
('S8','C',0);
insert into @Mapping_OUT values
('S1','X'),
('S1','S'),
('S2','P'),
('S2','R'),
('S3','O'),
('S4','Z'),
('S5','Y'),
('S6','M'),
('S7','N'),
('S8','T');
--SELECT * FROM @Mapping_OUT
--SELECT * FROM @Mapping_IN
--SELECT * FROM @Order
--CTE
;with MapItemList as
(
select MapID, count(*) as NumberOfItemsInList
from @Mapping_IN
group by MapID
),
MatchesList as
(
select MI.MapID, O.OrderID, count(*) as NumberOfMatches
from @Order as O
inner join @Mapping_IN as MI
on O.OrderItem = MI.OrderItem
group by MI.MapID, O.OrderID
),
FindMatches as
(
select ML.*, row_number() over (partition by ML.OrderID order by ML.NumberOfMatches desc) as RN
from MatchesList as ML
inner join MapItemList as MA
on ML.MapID = MA.MapID and ML.NumberOfMatches = MA.NumberOfItemsInList
)
select O.OrderID, MO.OrderItem, O.Quantity, O.Price
from FindMatches as FM
inner join @Mapping_IN as MI
on FM.MapID = MI.MapID and MI.MajorProduct = 1
inner join @Order as O
on O.OrderID = FM.OrderID and MI.OrderItem = O.OrderItem
inner join @Mapping_OUT as MO
on FM.MapID = MO.MapID
where RN = 1; -- Only allows the best match available i.e. most amount of products matched
select O.OrderID, MO.OrderItem, O.Quantity, O.Price
from (select ML.*, row_number() over (partition by ML.OrderID order by ML.NumberOfMatches desc) as RN
from (select MI.MapID, O.OrderID, count(*) as NumberOfMatches
from @Order as O
inner join @Mapping_IN as MI
on O.OrderItem = MI.OrderItem
group by MI.MapID, O.OrderID) as ML
inner join (select MapID, count(*) as NumberOfItemsInList
from @Mapping_IN
group by MapID) as MA
on ML.MapID = MA.MapID and ML.NumberOfMatches = MA.NumberOfItemsInList) as FM
inner join @Mapping_IN as MI
on FM.MapID = MI.MapID and MI.MajorProduct = 1
inner join @Order as O
on O.OrderID = FM.OrderID and MI.OrderItem = O.OrderItem
inner join @Mapping_OUT as MO
on FM.MapID = MO.MapID
where RN = 1; -- Only allows the best match available i.e. most amount of products matched
Please HELP !! Again thanks a lot for taking time out of your schedule to reply for my post !!