Find Duplicates (Sort Of) - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Find Duplicates (Sort Of)

Author

Topic

sashidhar_n
Starting Member

3 Posts

Posted - 2011-12-13 : 15:27:41

I have a table which contains the orders placed by a customer. An order can consist of items and sub-items. A sample layout is provided below

Period CustomerID ItemNum SubItemNum Quantity Value
201110 1 1 1 10 20
201110 1 1 2 10 40
201110 2 1 1 10 20
201110 2 1 2 10 40
201110 3 1 1 10 20
201110 3 1 2 20 80
201110 4 1 1 20 40
201110 4 1 2 20 80

I would like to know how to find out the different customers who have placed an order for exactly the same quantity of item/sub-items (and also the whole order) in one period. CustomerIDs 1 and 2 in the above example fit the conditions. Can anyone tell me how to code for this?

Thanks

Sashi

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-13 : 17:22:18

The requirements are not clear to me. Arewe trying to find the same Item and Sub-item and Quantity? If so, why do customers 1 and 2 fit the pattern when the sub-item is different? If customers 1 and 2 fit then why doesn't customer 4 fit, also?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

sashidhar_n
Starting Member

3 Posts

Posted - 2011-12-14 : 11:10:12

Bustaz

I want to know which customers placed the exact same order in a period, i.e., the same item, same sub-item and same quantity for each sub-item. Customers 1 and 2 placed an order for Item 1, Sub-item 1 Qty 10 and Item 1, Sub-item 2 Qty 10. Customers 3 and 4 have differing quantities.

Hope I am clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 11:20:25

[code]
SELECT Period,
CustomerID,
ItemNum,
SubItemNum,
Quantity,
Value
FROM
(
SELECT Period,
CustomerID,
ItemNum,
SubItemNum,
Quantity,
Value,
COUNT(1) OVER (PARTITION BY Period,ItemNum,SubItemNum,Quantity) AS Cnt
FROM Table
)t
WHERE Cnt >1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sashidhar_n
Starting Member

3 Posts

Posted - 2011-12-14 : 14:57:04

Visakh

The output of your code gives me all the records who have the same item/sub-item, but not the whole order.

PeriodID CustomerID ItemNumr SubItemNum Quantity Value
201110 1 1 1 10 20
201110 2 1 1 10 20
201110 3 1 1 10 20
201110 1 1 2 10 40
201110 2 1 2 10 40
201110 3 1 2 20 80
201110 4 1 2 20 80

What I am interested in is knowing the customers who placed the exact same order (i.e., the same combination of item/sub-items and the same quantity for each item/sub-item combination) in WHOLE. In the output above, I am only interested in rows 1, 2, 4 and 6 in combination.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 00:07:43

sorry i didnt get what you mean by WHOLE order. I cant see any field in your field which indicates which order that item,subitem,quantity is a part of

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources