Duplicate records

company_ID transaction_date reference_no
1 2017-12-01 100
1 2017-12-08 200
1 2017-12-05 300
1 2017-12-10 100
1 2015-02-10 400
1 2016-05-10 200
1 2016-06-20 300
3 2016-07-20 600
3 2016-08-20 700

---I need to get the RECORD with duplicate reference_no from dates 2017-12-01 to 2017-12-10 where company_ID =1
---Then check if the reference_no from dates 2017-12-01 to 2017-12-10 have duplicate in other years (company_ID =1)

expected result:
company_ID reference_no
1 100
1 200
1 300

Thanks

Anybody Please?

hi

I have just started looking at it ..

Please give me some time

Thanks

Hi

Is this what you are looking for ?

Please let me know

thanks

Data Create Script

DROP TABLE harish_dups

go

CREATE TABLE harish_dups
(
company_id INT NULL,
transaction_date DATETIME NULL,
reference_no INT NULL
)

go

INSERT INTO harish_dups
VALUES (1,
'2017-12-01',
100)

INSERT INTO harish_dups
VALUES (1,
'2017-12-08',
200)

INSERT INTO harish_dups
VALUES (1,
'2017-12-05',
300)

INSERT INTO harish_dups
VALUES (1,
'2017-12-10',
100)

INSERT INTO harish_dups
VALUES (1,
'2015-02-10',
400)

INSERT INTO harish_dups
VALUES (1,
'2016-05-10',
200)

INSERT INTO harish_dups
VALUES (1,
'2016-06-20',
300)

INSERT INTO harish_dups
VALUES (3,
'2016-07-20',
600)

INSERT INTO harish_dups
VALUES (3,
'2016-08-20',
700)

go

Solution Script

SELECT company_id,
reference_no
FROM harish_dups
WHERE company_id = 1
AND transaction_date BETWEEN '2017-12-01' AND '2017-12-10'
INTERSECT
SELECT company_id,
reference_no
FROM harish_dups
WHERE company_id = 1
AND Year(transaction_date) <> 2017

1 Like

A couple of alternative methods:

Query 1

´´´sql
select company_id
,reference_no
from yourtable
where company_id=1
group by company_id
,reference_no
having sum(case
when transaction_date>=cast('20171201' as date)
and transaction_date<cast('20171211' as date)
then 1
else 0
end
)>1
and sum(case
when transaction_date>=cast('20171201' as date)
and transaction_date<cast('20171211' as date)
then 0
else 1
end
)>1
;
´´´

Query 2

´´´sql
select a.company_id
,a.reference_no
from yourtable as a
where a.company_id=1
and a.transaction_date>=cast('20171201' as date)
and a.transaction_date<cast('20171211' as date)
and exists (select 1
from yourtable as b
where b.company_id=a.company_id
and b.reference_no=a.reference_no
and not (b.transaction_date>=cast('20171201' as date)
and b.transaction_date<cast('20171211' as date)
)
)
group by a.company_id
,a.reference_no
;
´´´

1 Like