September 29, 2013 at 10:26 am
I have two tables,
1st Table is the Order Table which has order no,old items, price and their quantity.
2nd Table is Mapping Table which has mapping for each unique combinations of the old order items.
3rd Table is the desired output.
Some notes:
1)Whenever in an order there is a combination of item1,item2,item3 of the mapping table it should retrieve output1 and output 2 for that, when in order there is a combination of item1 and item 2 of the mapping table it should retrieve output1 and output 2 for that, when in order there is only item 1 present it should retrieve output1 and output 2 for that.
2)Also, price of item2 and item3 will always be 0. The output table should take the price and quantity of Item1 always.
3) Its not necessary that Order No will be sequential. It can be like 456,789,989 etc.
DDL and DML for my question:
declare @orders table (
OrderNo int,
OrderItem varchar(10),
Quantity int,
Price money
)
declare @mapping table (
Item1 varchar(10),
Item2 varchar(10),
Item3 varchar(10),
Output1 varchar(10),
Output2 varchar(10)
)
insert into @orders
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
values
('A', 'B', 'C', 'X', 'S'),
('A', 'B', '', 'P', 'R'),
('A', '', '', 'O', ''),
('D', '', '', 'Z', ''),
('E', 'B', 'C', 'Y', ''),
('Q', '', '', 'M', ''),
('J', 'B', 'C', 'N', '')
-- Output Expected
declare @output table (
OrderNo int,
NewItem varchar(10),
Quantity int,
Price money)
insert into @output
values
(1, 'X', 3, 960),
(1, 'S', 3, 960),
(1, 'Z', 2, 200),
(2, 'Y', 5, 100),
(3, 'M', 6, 1000),
(4, 'P', 3, 300),
(4, 'R', 3, 300),
(5, 'O', 7, 4000)
select * from @orders
select * from @mapping
select * from @output
-- Solution that I tried giving wrong output
DECLARE @OutputTable TABLE (orderNo int, newItem varchar(1), quantity int, price money)
;
INSERT INTO @OutputTable(orderNo, newItem, quantity, price)
SELECT o.orderNo, m.output1, o.quantity, o.price
FROM @mapping as m INNER JOIN @orders as o
ON m.item1 = O.orderItem AND o.price != 0 AND m.output1!=''
INSERT INTO @OutputTable(orderNo, newItem, quantity, price)
SELECT o.orderNo, m.output2, o.quantity, o.price
FROM @mapping as m INNER JOIN @orders as o
ON m.item1 = O.orderItem AND o.price != 0 AND m.output2!=''
SELECT * FROM @OutputTable ORDER BY orderNo
I have also attached the solution I tried from my end.
Thanks in advance.
September 29, 2013 at 3:04 pm
Given the requirement I think that the design of the mapping table needs changing to allow the query to be written in a way to produce the correct results and be flexible for the future. I would propose dividing the mapping table into 2 tables (IN and OUT). Your examples show cases where upto 3 orderitems can be passed in and upto 2 passed out.
Using the table structure below would give the flexibility required.
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))
Your example data would then be :
insert into @Order values
(1,'A',3,960),(1,'B',1,0),(1,'C',1,0),(1,'D',1,0),
(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)
insert into @Mapping_OUT values
('S1','X'),('S1','S'),('S2','P'),('S2','R'),('S3','O'),('S4','Z'),('S5','Y'),('S6','N'),('S7','N');
Your requirements state that all orderitems must be present. As OrderID (containing ABC) could match 3 of the mapping rows I have assumed that the one with the most products matching would be required. 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.
Query to give the result set you outline (first using CTE and second using subqueries).
--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
-- Subquery
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
Fitz
September 29, 2013 at 5:04 pm
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 !!
September 29, 2013 at 10:50 pm
Hi Mark,
I figured it out myself.
Instead of using row_number() if I use rank() or dense_rank() it would take all the combinations keeping the value of RN same for the highest number of match.
Again thanks a lot !!
I was stuck on it for 4 days and you came up as a life savior.
Regards,
Vivek
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply