I am trying to write a query to count duplicate returned items to see how many customers are purchasing a trouser with different size and returning one. e.g
Ordernumber
ProductCode
Size
Descripton
Sale
Return
1300032
965625617
32
Trouser Short
1
0
1300032
965625617
36
Trouser Long
1
-1
1545335
965992377
42
Trouser Regular
2
0
1576435
965353453
36
Trouser Long
2
-1
Order num: 1300032, this customer purchased 2 trouser with a different size and returned one.
I am trying to produce a result something like below
Total Order
Duplicate Returned Order
3
1
How to form a query to achieve the above, any help will be highly appreciated.
Ordernumber is not the same as customer, and there could be two trousers of different sizes returned for the same ordernumber. But ignoring that, something like this should get you close, although without usable test data it's impossible for me to test it:
SELECT COUNT(*) AS [Total Order],
SUM(CASE WHEN Total_Return = 0 THEN 0 ELSE 1 END) AS [Duplicate Returned Order]
FROM (
SELECT Ordernumber, Size, SUM(Sale) AS Total_Sale, SUM(Return) AS Total_Return
FROM dbo.table_name
WHERE Description LIKE 'Trouser%'
GROUP BY Ordernumber
) AS derived
I suspect something like the following will get what you want in terms of finding all the orders where a customer ordered multiples of the same thing in different sizes and then returned some of them. There are edge cases that it doesn't cover, such as a customer order 2 size 36 trousers and 2 size 34 and then returning 1 of each, the logic may get considerably more complex if that's important to distinguish but the principle is roughly the same.
select
OrderNumber,
ProductCode
from #orders
group by OrderNumber,ProductCode
having
count(size) > 1 -- More than one size of same item
and sum(sale) + sum([return]) > 0 -- Not all were returned
and min([return]) <> 0 -- but at least some were returned
order by OrderNumber,ProductCode
It should be fairly straightforward to integrate that into a full overview report if needed