I need to find customers who have made identical orders. (Using T-SQL)
Order
OrderID Customerer
1 2
2 5
3 6
4 2
5 4
6 6
7 8
OrderLine
OrderLineID OrderID OrderDate OrderType Quantity Reference
1 1 01/01/2011 1 1 Coca Cola
2 1 01/01/2011 1 3 Tea
3 2 02/02/2011 2 1 Coffee
4 2 02/02/2011 2 2 Solo
5 2 03/02/2011 1 1 Soda
6 3 03/02/2011 1 3 Tea
7 3 03/02/2011 1 1 Coca Cola
8 4 05/06/2011 1 1 Beer
9 5 06/06/2011 2 1 Tea
10 5 06/06/2011 2 1 Coca Cola
11 6 07/07/2011 1 1 Coffee
12 6 07/07/2011 1 2 Solo
13 6 07/07/2011 1 1 Soda
14 6 07/07/2011 1 1 Beer
15 7 08/08/2011 1 1 Beer
Here orders with OrderID 1 and 3 are considered to be identical because the number for orderlines, "Quantity" and "Reference" are identical on both orders. Meaning that customer 2 and 6 have placed identical orders.
Order 5 are not identical to order 1 and 3 because Quantity differ.
Order 2 are not identical to order 6 because orderlines differ.
Order 4 and 7 are also identical.
I am searching for a ressult like this:
IdenticalOrders
OrderID CustomeerID
1 2
3 6
4 2
7 8
It seems like an easy task, but I just can't understand where to start.
(I am still new to t-sql :-) )
This is an extension of Martin's second suggestion. This will show all matching combinations without any repetitions.
;With FmtOL(customer, orderid, complete_order) as
(
SELECT customer, orderid, complete_order
FROM Order O
cross apply ( SELECT CAST(Quantity AS VARCHAR(30))
+ '~' + Reference + '~~'
FROM OrderLine OL
WHERE OL.OrderID = O.OrderID
ORDER BY Reference ,
Quantity
FOR
XML PATH('')
) T ( complete_order )
)
SELECT T1.OrderId,
T1.Customer,
STUFF(C1.a, 1, 2, '') as [SameAs]
FROM FmtOL T1
Cross apply ( SELECT '; ' + 'Customer ' + Cast(T2.Customer as varchar(30))
+ '''s order ' + Cast(T2.OrderID as varchar(30))
FROM FmtOL T2
WHERE T1.Customer < T2.Customer
AND T1.OrderId < T2.OrderId
AND T1.complete_order = T2.complete_order
order by ';' + Cast(T2.Customer as varchar(30))
+ '''s order ' + Cast(T2.OrderID as varchar(30))
, t2.orderid
for xml path('')
) C1 (a)
where C1.a is not null
Results should look like this:
OrderId Customer SameAs
1 2 Customer 6's order 3
4 2 Customer 8's order 7
Here's one way.
SELECT O1.OrderID ,
O1.Customer ,
O2.OrderID ,
O2.Customer
FROM [Order] O1
JOIN [Order] O2 ON O1.OrderID < O2.OrderID
AND O1.Customer <> O2.Customer
WHERE NOT EXISTS ( SELECT Quantity ,
Reference
FROM OrderLine
WHERE O1.OrderID = OrderLine.OrderID
EXCEPT
SELECT Quantity ,
Reference
FROM OrderLine
WHERE O2.OrderID = OrderLine.OrderID )
AND NOT EXISTS ( SELECT Quantity ,
Reference
FROM OrderLine
WHERE O2.OrderID = OrderLine.OrderID
EXCEPT
SELECT Quantity ,
Reference
FROM OrderLine
WHERE O1.OrderID = OrderLine.OrderID )
You can also use XML PATH to simulate GROUP_CONCAT then JOIN the two result sets
DECLARE @T TABLE
(
OrderId INT PRIMARY KEY,
Customer INT ,
complete_order VARCHAR(MAX)
)
INSERT INTO @T
SELECT *
FROM [Order] O
CROSS APPLY ( SELECT CAST(Quantity AS VARCHAR(30))
+ '~' + Reference + '~~'
FROM OrderLine OL
WHERE OL.OrderID = O.OrderID
ORDER BY Reference ,
Quantity
FOR
XML PATH('')
) T ( complete_order )
SELECT T1.OrderId,
T1.Customer
FROM @T T1
WHERE EXISTS ( SELECT *
FROM @T T2
WHERE T1.Customer <> T2.Customer
AND T1.OrderId <> T2.OrderId
AND T1.complete_order = T2.complete_order )
Here's the most simple approach.
-- sample table
create table x
(
LineId int identity(1, 1)
,InvoiceFk int
,ProductFk int
,Quantity int
)
-- sample data
insert into x
(InvoiceFk, ProductFk, Quantity) values
(11, 1, 1)
,(11, 2, 1)
,(11, 3, 1)
,(12, 1, 2)
,(12, 2, 2)
,(12, 3, 2)
,(13, 1, 3)
,(13, 2, 3)
,(13, 3, 3)
-- your order, probably from a parameter
declare @order table
(
InvoiceFk int
,ProductFk int
,Quantity int
)
insert into @order
(InvoiceFk, ProductFk, Quantity) values
(14, 1, 1) -- duplicate invoice 11
,(14, 2, 1)
,(14, 3, 1)
-- your order unique checksum
declare @orderCheck int
select @orderCheck = checksum_agg(checksum(ProductFk, Quantity))
from @order
-- test your order in existing data
declare @match int
select @match =
(
select TOP 1 InvoiceFk from
(
select
InvoiceFk
,checksum_agg(Col1) as Col2
from
(
select
InvoiceFk
,checksum(productfk, quantity) as Col1
from x
) as T1
group by
InvoiceFk
) as T2
where
T2.Col2 = @orderCheck
)
-- evaluate if your order is unique or not
if (@match is not null)
begin
print 'Identical to invoice: ' + Str(@match);
end
else
begin
print 'Order is unique';
end
-- clean up sample table
drop table x
Best of luck!
