dimanche 11 septembre 2016

Return duplicates by comparing each records of the same table in the sql server

I have table like below.I wanted to get the duplicate records.Here the condition

I need find duplicate on subscriber whoes status = 1 i.e. active and for current year it has the multiple records by compairing start_date and end_date. I have around more than 5000 records in the DB.Showing here few sample example.

id      pkg_id  start_date  end_date    status  subscriber_id
2857206 9128    8/31/2014   8/31/2015   2       3031103
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857206 9128    8/31/2013   8/31/2015   2       3031104
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104
3066285 10308   8/5/2016    8/4/2018    2       3031105

I tried below's query but not worked for all records:

select * from dbo.consumer_subsc 
where subscriber_id in (3031103) and status=1 and year(getdate()) >= year(start_date) and
year(getdate()) <= year(end_date) and subscriber_id in (select T.subscriber_id from (select subscriber_id,count(subscriber_id) as cnt from dbo.consumer_subsc where
status=1 group by subscriber_id having count(subscriber_id) > 1)T
) order by subscriber_id desc

The problem is I'm not able to find a way, where each row can be compared with each other with above date condition.I should get the result like below as duplicate:

id      pkg_id  start_date  end_date    status  subscriber_id
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104

Aucun commentaire:

Enregistrer un commentaire