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


Output based on Mapping Table and Orders Table


Output based on Mapping Table and Orders Table

Author
Message
vivekkam
vivekkam
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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 @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.

[url=http://s10.This image host is not supported, please use another/93j6lzb3d/Query_Image.png][/url]
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4803 Visits: 1652
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
vivekkam
vivekkam
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 6
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 !!
vivekkam
vivekkam
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 6
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
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