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