Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Output based on Mapping Table and Orders Table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, September 29, 2013 10:26 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, January 29, 2016 3:04 PM Points: 6, Visits: 6
 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 @ordersvalues (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 @mappingvalues ('A', 'B', 'C', 'X', 'S'), ('A', 'B', '', 'P', 'R'), ('A', '', '', 'O', ''), ('D', '', '', 'Z', ''), ('E', 'B', 'C', 'Y', ''), ('Q', '', '', 'M', ''), ('J', 'B', 'C', 'N', '')-- Output Expecteddeclare @output table ( OrderNo int, NewItem varchar(10),Quantity int,Price money)insert into @outputvalues (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 @ordersselect * from @mappingselect * from @output-- Solution that I tried giving wrong outputDECLARE @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 orderNoI have also attached the solution I tried from my end.Thanks in advance.[url=http://s10.This image host is not supported, please use another/93j6lzb3d/Query_Image.png][/url]
Post #1499760
 Posted Sunday, September 29, 2013 3:04 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 18, 2016 1:24 AM Points: 3,662, Visits: 1,510
 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-- Subqueryselect 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
Post #1499777
 Posted Sunday, September 29, 2013 5:04 PM
 Forum Newbie Group: General Forum Members Last Login: Friday, January 29, 2016 3:04 PM Points: 6, Visits: 6