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