Find duplicate values with different year in SQL

Question

How can I search duplicate records in a table but has different years.

My sample data:

Cus_No  Item_No Ord_Dt  Orders 
1       A       2016    1
1       A       2017    2
1       B       2016    1
2       B       2015    1
2       B       2018    1

Output needed

Cus_No  Item_No Ord_Dt  Orders 
1       A       2016    1 
1       A       2017    2
2       B       2015    1
2       B       2018    1

I am trying to collect all records with the same Cus_No, the same Item_No that has any value in Orders and exist in any year in Ord_dt. The reason is, I need to find those items with the same customer number that has orders from all years. I am using MS Query and this is the SQL statement I tried but still displays all records.

SELECT `'table'`.Cus_No, `'table'`.Item_No, `'table'`.Ord_Dt, `'table'`.Orders
FROM `'table'`
WHERE (`'table'`.Orders>=1) AND (`'table'`.Ord_Dt In ('2016','2017'))

Please include the sample data directly in your question, _not_ as an image link. And also show us the expected output. — Tim Biegeleisen, Feb 28 '18 at 01:59
*How can I search duplicate records*... what do you want to do with them when you search them? — lurker, Feb 28 '18 at 02:01
The SQL you've provided is not compatible with SQL Server (SQL Server doesn't use backticks). And MS Query is a truly ancient SQL editor program. — Dai, Feb 28 '18 at 02:04
@TimBiegeleisen That record has no other order/s from any year/s. Considering that the item is a sample product. A particular product or item should only be given to a customer. An item or product that was ordered or given more than once is considered a duplicate order. — user3113137, Feb 28 '18 at 03:32

score 0 · Answer 1 · answered Feb 28 '18 at 03:36

0

Below query returns duplicates -

select * from test where (cus_no, item_no) in (
select Cus_No, item_no from test group by Cus_No, item_no having count(*) > 1)

answered Feb 28 '18 at 03:36

Ajay

532
3
19

score 0 · Answer 2 · answered Feb 28 '18 at 03:41

Stab in the dark:

with agg as (
    select cust_no, item_no from T
    group by cust_no, item_no
    where ord_dt in (...)
    having count(*) = <num years in list>
)    
select *
from T t inner join agg a
    on  a.cust_no = t.cust_no
        and a.item_no = t.item_no;

score 0 · Accepted Answer · answered Feb 28 '18 at 03:42

0

Assuming you want to identify records corresponding to customer items which appeared across more than one year, we can try the following:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Cus_No, Item_No
    FROM yourTable
    GROUP BY Cus_No, Item_No
    HAVING COUNT(DISTINCT Ord_Dt) > 1
) t2
    ON t1.Cus_No  = t2.Cus_No AND
       t1.Item_No = t2.Item_No

answered Feb 28 '18 at 03:42

Tim Biegeleisen

502,043
27
286
360

There is one more problem i have. I tried replacing yourTable with the actual table name `Table$` in MS Query statement but returns an error, SQL Query can't be represented graphically. I also tried looking if this link helps [link](https://stackoverflow.com/questions/2804299/what-is-the-microsoft-query-syntax-for-subqueries) – user3113137 Feb 28 '18 at 04:52
I don't know your database. Really most of the answers given here should get you close, at least in ANSI SQL. – Tim Biegeleisen Feb 28 '18 at 04:53
One last question. What is the difference between the inner join you have given from this statement? 'code' SELECT `Table$`.Cus_No, `Table$`.Item_No FROM `Table$` `Table$` GROUP BY `Table$`.Cus_No, `Table$`.Item_No HAVING (Count(*)>1) – user3113137 Feb 28 '18 at 04:56
That might be OK, if you are certain that a given year only appears once per item and customer. – Tim Biegeleisen Feb 28 '18 at 04:58
Thank you so much Tim. I got the idea now. Will work on running it MS Query. – user3113137 Feb 28 '18 at 04:59

Find duplicate values with different year in SQL

3 Answers3