Output based on Mapping Table and Order Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Output based on Mapping Table and Order Table

Author

Topic

vivekkam
Starting Member

5 Posts

Posted - 2013-09-29 : 12:31:31

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-01 : 04:43:22

I think you need to get orders where item1, 2, 3 exist and insert that for order1, order2
Then those where item1, 2 exist but not item1, 2, 3 and insert that for order1, order2
Then those where item1 exists but not item1, 2 and insert that for order1, order2

I think it can be done in a single statement (or maybe 2) by left joining on item1, 2, 3 using 3 derived tables then checking for nulls.
I'll look at it in a bit when I get time.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-01 : 05:04:33

What would happen if orderno 1 also had a row for orderitem E.
Then it would match on A,B,C and E,B,C - should this end up with 3 output rows?

I think this would be easiest by creating a temp table of the orders then gong through the mapping table row by row - starting with mapping rows on 3 items and deleting rows from the temp table when they are processed.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-01 : 06:01:39

How about



declare @o table (
 OrderNo int,
 OrderItem varchar(10),
 Quantity int,
 Price money
 )

declare @m table (
 Item1 varchar(10),
 Item2 varchar(10),
 Item3 varchar(10),
 Output1 varchar(10),
 Output2 varchar(10) ,
 seq int
 )

insert @m select *, seq = row_number() over (order by item3 desc, item2 desc, item1 desc) from @mapping
insert @o select * from @orders
select * from @m

declare @seq int = 0
while @seq < (select max(seq) from @m)
begin
	select @seq = @seq + 1
	insert @OutputTable(orderNo, newItem, quantity, price)
	select o.orderNo, m.Output1, o.Quantity, o.Price
	from @m m
		join @o o
			on m.item1 = o.orderItem
	where (exists (select * from @o o2 where m.item2 = o2.orderItem and o2.OrderNo = o.OrderNo) or m.item2 = '')
	and (exists (select * from @o o3 where m.item3 = o3.orderItem and o3.OrderNo = o.OrderNo) or m.item3 = '')
	and m.seq = @seq

	insert @OutputTable(orderNo, newItem, quantity, price)
	select o.orderNo, m.Output2, o.Quantity, o.Price
	from @m m
		join @o o
			on m.item1 = o.orderItem
	where (exists (select * from @o o2 where m.item2 = o2.orderItem and o2.OrderNo = o.OrderNo) or m.item2 = '')
	and (exists (select * from @o o3 where m.item3 = o3.orderItem and o3.OrderNo = o.OrderNo) or m.item3 = '')
	and m.seq = @seq
	and m.Output2 <> ''

	delete @o
	from @m m
		join @o o
			on m.item1 = o.orderItem
	where (exists (select * from @o o2 where m.item2 = o2.orderItem and o2.OrderNo = o.OrderNo) or m.item2 = '')
	and (exists (select * from @o o3 where m.item3 = o3.orderItem and o3.OrderNo = o.OrderNo) or m.item3 = '')
	and m.seq = @seq
end
SELECT * FROM @OutputTable ORDER BY orderNo

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources