- test 1.
-- orders are defined:
declare @orders table (OrderNbr int, NoteId int) -- OrderNbr is PK
insert @orders (OrderNbr, NoteID)
select 1, 1 union select 2, 2 union select 3, 1 union select 4, 1 union select 5, 3 union select 6, 2
select * from @orders
OrderNbr NoteId
1 1
2 2
3 1
4 1
5 3
6 2
-- list the orders that have duplicate note ID. But not first occurrence - so the order 1 is ok, but 3 is not
-- Result should be:
-- 3
-- 4
-- 6
-- test 2 Optional.
-- lots of items
declare @lots table (id varchar(10), qty integer)
-- orders
declare @orders table (num varchar(10), qty integer)
insert @lots (id, qty)
select 'l1', 5
union all select 'l2', 12
union all select 'l3', 8
insert @orders (num, qty)
select 'o1', 2
union all select 'o2', 8
union all select 'o3', 2
union all select 'o4', 5
union all select 'o5', 9
union all select 'o6', 5
-- task - list all pairs of lots-orders. So what lot will be put in what order.
-- example: o1-l1, o2-l1, o2-l2, o2-l3, ....