SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Output based on Mapping Table and Order Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vivekkam
Starting Member

USA
5 Posts

Posted - 09/29/2013 :  12:31:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/01/2013 :  04:43:22  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/01/2013 :  05:04:33  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 10/01/2013 05:06:28
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/01/2013 :  06:01:39  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000